A drop down list is a useful way to limit what users enter into particular cells. The values contained within the dropdown list are static, with no easy way to add new values when necessary. This article will show you how to use a table to create a dynamic list for cell validation so the drop down list can be easily changed by the user.
Consider an example where a user has to enter into A2 the name of a component that has failed from a pump. In the past, 3 components have failed; the bearings, the shaft or the coupling. These are listed in Column F below.
The sheet can be set up using Data Validation so that only the values from the Components list are selectable from a drop down list. However, if a new component is added to the list, the Data Validation will not automatically update, and will have to be updated manually.
Creating a Dynamic Drop Down List
Set up the list as a table, by highlighting the list, and pressing Ctrl + T, or by selecting Insert -> Table.
A new window will pop up. Tick “My table has headers” and select OK.
The list is now saved as a table, which in this case is named Table2. Click on the cell A2, and go to Data -> Data Validation -> Data Validation, as shown below.
A new screen will pop up. Under “Allow” select “List” and under “Source” select the 3 components in the table, so that it reads “=$F$2:$F$4”, as shown below, and select OK.
Cell A2 will now have a drop down list that corresponds to the values in this table, as shown below.
Adding an Item
If a different component failed, such as an impeller, it can be added to the list by typing impeller into the next cell in Column F. When you hit enter, the table dimensions will extend to include it, and the drop down list in A2 will also accommodate this, as shown below.
To see other Excel tips, traps and VBA code, see our Excel Tools resource page here.