Use Excel Formulas to Create a Variable Drop-Down List

Home » Excel-Formulas » Variable-Drop-Down-List

Search this site: Custom Search

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:

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:

This is shown in the spreadsheet below.

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:

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

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.