Microsoft Excel - Creating Drop-Down lists that depend on the value of another list(s)

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial; however the process is the same for 2007. If you are using 2003 or earlier, the process again is the same, however you need to navigate the File/Edit/Menu, instead of using the ribbon.

Attached is the file I used for this tutorial.

For this example I am using the following categories of data:

Foreign or Domestic Automobiles

Domestic = Chevy, Dodge or Ford

Foreign = Audi, BMW or Toyota

Models for each maker, i.e. Dodge = Avenger, Caliber, Challenger, etc.

In 3 clicks a user can select a Chevy Malibu by selecting -> Domestic -> Chevy -> Malibu

This may be used in a number of ways, such as guiding a user to the correct type of form based on which drop-down is selected or which procedure to follow. The data for this example is stored within the file, however it could also be linked to a Database, XML or another data source outside of Excel.

To being I created two worksheets: WORKSPACE (where the user will make selections) and DATA (where all our information will be stored).

Using row 1 on worksheet DATA I named my “headers” for each row of data:

Notice that I used INPUTLIST for my “initial listing” and then below it I added my first user selectable data points: DOMESTIC and FOREIGN

Next I created two more header entries next to INPUTLIST matching my input choices: DOMESTIC and FOREIGN

Filling in the auto brands below their corresponding column of DOMESTIC or FOREIGN we get:

Now we need to add a header row for each auto brand, so we get the following:

Finally we put each model of vehicle under their corresponding manufacturer:

With all of our column headings in place and our data listed below each, we now need to define some names for Excel to use to populat our lists and ultimately our drop-down menues.

To define our first name which will be column A (INPUTLIST) we need to highlight all data below the list title, in this example that would be cells A2 and A3. Then select Formula -> Define Name and input a Name in the box provided. We are going to use INPUTLIST

Then we wil select the data below the list lable in the next column (B) selecting B2:B(last row), thus B2:B4 and name this DOMESTIC:

We continue this until all of the lables in row 1 have been entered. To view all of our Defined Names, we can click on the Name Manager located in the Formula Secton of the Ribbon:

Within the name manager we can see all of our Names, what their values are, what they refer to and the scope that they encompass:

Now that we have created our data structure we can start to build the user drop-downs, so we need to click over to worksheet WORKSPACE.

I have decided to use cell B2 as my first Label and have inputted the text US or Worldwide

The first drop-down will reside below “US or Worldwide” in cell B3, so I select B3 and on the Ribbon I go to DATA -> Data Validation -> Data Validation…

This will bring up the Data Validation Menu. On the Settings Tab, I change Allow: from Any value to List, which then shows the “Source:” box.

Since this is our first input, we are going to use the Defined Name: INPUTLIST

We can simply type the equal sign followed by the list name, such as:

=INPUTLIST

In this example, this is easy enough, however we could have many names and mistyping a defined name would cause errors, so I recommend we select the name from our named list.

Selecting the arrow at the end of the source list


Changes the Data Validation box to just one row:

In this state, we can go up to the ribbon and click on the FORMULA Tab, and in the defined names area, we see that the “Use in Formula” option is available:

Clicking on “Use in Formula” we get a list of defined names. Since we want INPUTLIST, we click on that:

Excel now typed in for us =INPUTLIST

Clicking the arrow on the right returns us to the Data Validation box and we can now select OK.

The result is as follows:

In column C, cell 2, we can enter our next Heading; I am going to use Manufacturer.

To populate the drop-down list below Manufacturer based on what was selected in “US or Worldwide” we cannot simple add a list. We need some logic that says, if the cell next to me in column B is DOMESTIC, then list CHEVY, DODGE or FORD, if it is FOREIGN, then list AUDI, BMW or TOYOTA.

The solution for this is to select C3 and go to Data -> Data Validation and change the Allow: to List, like before. But in the Source Box we are going to type =INDIRECT(B3).

This formula says that whatever is in B3, we are using that as our list, Since DOMESTIC is a Defined Name with the data of CHEVY, DODGE or FORD, that is all that will show up, if DOMESTIC is selected.

Now I am going to add a label for Model in D2:

Again, selecting D3 (the cell below Model) and going to DATA -> Data Validation -> Changing Allow to LIST and using the source of =INDIRECT(C3) as Model is based on the Manufacturer we get our source for Model:

Once we have added all sources for our first row of data, we need to copy and paste the formula down the worksheet so that our users will have these drop-downs in place.

To do this, highlight B3 to D3, right click and select copy:

Next we can select the rows we want to have the drop-downs, I clicked on B4 and then highlighted down to B11, so my user would have 9 rows in total, Right clicked on the highlighted cells and went to Paste Special and in the second menu, I selected the Paste Special… at the bottom

This brings up the Paste Special menu with radio buttons for each of the options. Here I clicked Validation, as this is all we want to paste, and then clicked OK.

Now all of the drop-downs work in B3:B11 with column C’s options based on the selection in B and column D’s options based on the selection in C.


And that concludes this tutorial, hopefully you will be able to adapt this to your project!
EE-Article-Excel-Dynamic-Drop-Do.xlsx