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

redrumkev
CERTIFIED EXPERT
Published:
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).

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

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

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

Domestic or Foreign - HeaderFilling in the auto brands below their corresponding column of DOMESTIC or FOREIGN we get:

Domestic or Foreign - Header - DATANow we need to add a header row for each auto brand, so we get the following:

Domestic or Foreign - Adding NamesFinally we put each model of vehicle under their corresponding manufacturer:

Add Model NamesWith 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

Define Names 1Then 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:

Define Names 2We 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:

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

Data ManagerNow 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

US or WorldwideThe 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…

Data Validation RibbonThis 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.

Data Validation BoxSince 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

Source Arrow
Changes the Data Validation box to just one row:

Source Single RowIn 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:

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

Select the LIST you needExcel now typed in for us =INPUTLIST

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

The result is as follows:

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

Manufacturer LabelTo 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).

INDIRECT FormulaThis 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:

Model Label 2Again, 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:

Second INDIRECT Formula - C3Once 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:

Select and CopyNext 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

Paste SpecialThis 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.

Paste Special -> ValidationNow 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.

Final Product
And that concludes this tutorial, hopefully you will be able to adapt this to your project!
EE-Article-Excel-Dynamic-Drop-Do.xlsx
8
4,796 Views
redrumkev
CERTIFIED EXPERT

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.