A list is a powerful tool. It can help you to make your data entry simple and quick. You can use a drop-down list with your dashboards, charts, or even with normal data.
While using it, one thing always gave me nuts you need to update its data source whenever you add a new entry. The more you add data the more frequently you need to update it.
For this, the best solution is to use a dynamic list in which you don’t need to update the data source again and again. But first of all, let me show you an example that why your drop-down list should be a dynamic one. In the below example, we have two different drop-down lists in which we have used the same data source.
When you added a new entry into the source list, that entry is automatically updated in the second drop-down list.
On the other hand, there is no change in the normal drop-down list. And, if you want to update your list, you need to update your data source range.
If you are using Excel 2007 or above then you can use an Excel Table and in case you are still with Excel 2003, you can use a named range.
1. Using Excel Table to Create a Dynamic Drop-Down List
The best method to create a dynamic drop-down list is using an Excel table for source data.
Here are the simple steps to create a dynamic drop-down list in Excel.
- First of all, change your normal range into a table.
- Select your list.
- Go to ➜ Insert Table ➜ Tables ➜ Table.
- Click OK.
- Now the next step is to refer to the table range data source and for this, we need to use the below formula.
=INDIRECT(“Table5[Month]”)
- In the end, click OK.
Now you have a dynamic list that will update instantly when you update your source list.
If you try to directly refer to the table range as a source it will return an error like the one below.
2. Dynamic Range for a Drop Down List with OFFSET Function
If you are still using Excel 2003 or a lower version, you can use the offset function to create a dynamic range, and then you can use that dynamic range for creating a drop-down list. Here are the easy steps to create a drop-down list using a dynamic range.
- Go to Formulas ➜ Defined Names ➜ Name Manager ➜ Click New.
- In the name input box enter a name for the named range (Here I am using “monthList2003”).
- Enter the below formula in “Refers To” and click OK.
- Now you have a dynamic range “monthList2003” and you can use this range name to create a dynamic drop-down list.
How does this work
The formula which we have used to create a dynamic range will count the values from the list and expand the source range accordingly. This will make your drop-down list dynamic.
I tried the dropdown list with the table method but was unable to add text to an entry (which is a capability I need), however, with the offset method I can overwrite, modify or add to the dropdown item chosen (in the text/fx box). Do you know of a way to do this with the table method, maybe with a wildcard? The dynamics of tables are very useful.
Thanks Puneet for sharing such useful information. I normally use Dynamic Drop down list but the issue is if any cell of source sheet is protected then table do not update.
I want to create a dynamic drop down list which can automatically sort alphabetically or have search option as the list I have to deal with have thousands of names in it.
Please share
Informatic Thanks
Very good post!
For those interested in learning more about drop down lists, check out this post:
http://www.masterdataanalysis.com/ms-excel/drop-down-lists-in-ms-excel/
It shows how to create dependent lists, how to display a tooltip when the list is selected, and many more tips.
Thnaks for sharing. Orlando
You too!
INDEX function also can be used for dynamic range name.
Armen, you are awesome. Thanks for sharing.