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.

image 1
 

  • 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.

 
image 2
 

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

 
image 3
 

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

 
image 4
 

  • 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

 
image 5
 

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

 

image 6
 

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

 

image 7

 

 

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

 
image 8
 

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

 
image 9
 

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

 
image 10
 

  • In Settings group click on Allow & select List

 
image 11
 

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

 
image 12
 

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

 
image 13
 

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

 
image 14

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube