Excel Tip: Dynamic drop down lists

Adam Norvill
Relken Engineering
Have a question or want to speak with Adam Norvill ? Contact us with your details.

Description

Drop Down List Teaser

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.

 

Example

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.

Screenshot 1

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.

Insert Table

A new window will pop up. Tick “My table has headers” and select OK.

List as a Table

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.

Insert Data Validation

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.

Data Validation Settings

Cell A2 will now have a drop down list that corresponds to the values in this table, as shown below. 

Drop down list inserted

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.

Drop down list is dynamic

 

To see other Excel tips, traps and VBA code, see our Excel Tools resource page here.