How to Create Dynamic Drop Down Lists in Microsoft Excel 2010

 

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 drop down list that belongs to the first list.

With the use of Data Validation feature in Excel, you can create your own custom error message when the user enters the information which is not predefined.

In this article we will use INDIRECT function & Name Manager.

 

INDIRECT: Returns the reference specified by a text string.

Syntax: =INDIRECT(ref_text,A1)

ref_text is a reference to a cell. If ref_text is not a valid cell reference, then INDIRECT function will return #REF error.

The second argument A1 refers to a logical value that specifies type of reference is contained in the cell ref_text.

If a1 is TRUE or omitted then ref_text is determined as A1-style reference.

If a1 is FALSE then ref_text is determined as R1C1 style reference.
 
Example: If value in cell A1 contains 10, B1 contains A1 & we use INDIRECT function in cell C1=INDIRECT(B1), then the result would be 10.
 
img1
 
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.
 
img2
 
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
 
img3
 
Make the Top row option checked & click on OK
 
img4
 
Press CTRL + F3 to open Name Manager, and you will find the list of Car Make
 
img5
 
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
 
img6
 
Click on Special & select Blanks then click on OK.
 
img7
 
This will select all the blank cells in the selected range.

Right click or press CTRL + – (dash)

Select Shift cells up & then click OK
 
img8
 
You can check the correct name list using CTRL + F3
 
img9
 
Now, the empty cells have been removed.

Click on range B2:B5

Click on Data tab

From Data Tools group, select Data Validation option
 
img10
 
Or use ALT + D + L shortcut keys for Data Validation
 
img11
 
In Settings group, click on Allow & select List.
 
img12
 
In Source enter the formula as =INDIRECT(A2)&click on Ok button
 
img13
 
Click on cell B2 to see the options available for selection.
 
img14
 
You can select the Model dependent on the value entered in column A.

In this way, you can create dynamic dropdown lists.

 



Example:


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>