How to create dynamic drop down list based on name range in Microsoft Excel

In this article you will learn how to create dynamic drop down list; after the selection from first list the second drop down list will be dynamically updated. We will use INDIRECT function & Name Manager.

The dynamic drop down list will be helpful for the end user to select the item from the excel drop down list that belongs to the first list.

With the use of Data Validation in excel you can create your own custom error message when the user enter the information which is not predefined.

In this article we will use INDIRECT function & Name Manager.
 
Let us take an example:

• We have Cars data i.e. their Make & Model
• The idea is to select the Make of the car & Model of the car should be dynamically updated & available for selection.

• The very first step is to make list of Models
• Select the range E2:H6 & press CTRL + SHIFT + F3 (shortcut for creating multiple lists)
• The Create Names from Selection window will appear.

• Make the Top row option checked not Left column & then click on ok.

• Press CTRL + F3 to open Name Manager you will find the list of Car Make

• Here the only problem is the list created contains the blanks like cell E6, F5, F6 & so on.
• We can easily remove the blanks using GO TO command.
• Select the range E2:H6 & press F5 key on the keyboard to open the Go to command

• Click on Special & select Blanks then click on ok.

• This will select all the blank cells in the selected range.
• Right click or press CTRL + - (dash)
• Select Shift cells up & then click ok

• You can check the correct name list using CTRL + F3

• Now, the empty cells have been removed.
• Click on range B2:B5
• Click on Data tab
• From Data Tools group select Data Validation option

• Or use ALT + D + L shortcut keys for Data Validation

• In Settings group click on Allow & select List

• In Source enter the formula as =INDIRECT(A2) & click on Ok button.

• Click on cell B2 to see the options available for selection.

• You can select the Model depending on the make entered in column A.