I have a request from friend to create a dynamic drop down list in excel and I would like to share with my blog reader...
Part 1:
- Open Excel and we start by creating a defined name,
- Select the Formulas Tab, then Define
- Enter "SomeRangeName" under the Name text box
- Enter =OFFSET(sheet1!$B$2,0,0,COUNTA(sheet1!$B:$B),1) under the Refers to text box
Note: You have to enter drop down items in single column you want here in above formula I have selected Sheet1 and column B, it will start filtering from Row B2.
Part 2:
To create a data validation list:
- Select the cell you want to show dropdown list eg. A1
- Select Data Validation under the Data Tab
- Select List under Allow
- Place the cursor in the Source text box and type formula "=SomeRangeName"
That's it.. now when ever you add items in "Column B" it will automatically populate in drop down list...
Part 1:
- Open Excel and we start by creating a defined name,
- Select the Formulas Tab, then Define
- Enter "SomeRangeName" under the Name text box
- Enter =OFFSET(sheet1!$B$2,0,0,COUNTA(sheet1!$B:$B),1) under the Refers to text box
Note: You have to enter drop down items in single column you want here in above formula I have selected Sheet1 and column B, it will start filtering from Row B2.
Part 2:
To create a data validation list:
- Select the cell you want to show dropdown list eg. A1
- Select Data Validation under the Data Tab
- Select List under Allow
- Place the cursor in the Source text box and type formula "=SomeRangeName"
That's it.. now when ever you add items in "Column B" it will automatically populate in drop down list...
No comments:
Post a Comment
Your feedback is always appreciated. I will try to reply to your queries as soon as time allows.Please don't spam,spam comments will be deleted upon reviews.