Use Excel Formulas to Create a Variable Drop-Down List

The following example shows you how to use Excel formulas to create a variable drop-down list.

Suppose you have a list of members of staff, split into teams. You might want to select a team from a drop down list, and then be able to select a member of that team from a second drop down list.

In order to do this, you need to begin with a list of members of each team, as shown below:

Data for Variable Drop Down Menu Example

You also need to create a range of cells that will show the team members for any selected team. In the case of the example above, the team name could be typed into cell G2, and then a formula in cells E2-E6 could return the members of the selected team.

In the example above, the following formula, which combines the Hlookup and the Row functions, would produce the names of the team members for any team name entered into cell G2:

=HLOOKUP($G$2, $A$1:$C$6, ROW(), 0)

This is shown in the spreadsheet below.

Formula for Variable Drop Down Menu Example

In the above spreadsheet the formula "=$G$2" is used to reflect the selected team name in cell E1 and the Hlookup formula in cells E2-E6 is used to display the members of the selected team.

A drop down list, that takes its possible values from cells E2-E6 will therefore also depend on the value of cell G2. To create a drop-down list in cell G4, from these values:

1. Select cell G4
2.

In Excel 2003:   Select the Data Menu from the top of your Excel spreadsheet and from within this menu, select the option Validation...

or

In Excel 2007 or Excel 2010:   Select the Data Tab from the top of your Excel spreadsheet and from within this menu, select the option Data Validation→Data Validation...

3. The Validation menu will appear. Within this menu :
  1. In the first box, select the option 'List'. This should cause further options to appear.
  2. Ensure the option 'In-cell dropdown' is ticked
  3. In the 'Source' option box, click on the symbol on the right of the box and use the mouse to select cells E2-E6 or type in "=$E$2:$E$6"
  4. Click OK

You will now have a drop-down menu in cell G4. This is shown below.

Final Result for Variable Drop Down Menu Example

Final Result for Variable Drop-Down List, Depending on Another Drop-Down List

To make your spreadsheet look that bit more professional, it is a good idea to hide columns A-C, which contain the original data, or place this data onto a different worksheet.

A further finishing touch could be to add a further drop-down list to cell G2, from which a team name can be selected. This therefore means that your final sheet has a drop-down list, that varies, depending on the selection from another drop-down list.


Click here for a further example of creating a drop-down list using excel data validation
Return to the Excel Formulas Page

Return to the ExcelFunctions.net Home Page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net