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.
(If you are not familiar with drop-down lists in Excel, you may want to first view the page on How to Create a Basic Drop-Down List in Excel).
How to Create a Variable Drop-Down List - Example
The spreadsheet below contains a list of members of staff, split into teams. We can use this data to create a drop-down list that contains just the staff members for a selected team, and automatically updates when a different team name is selected.
Step 1 - Create a List of Team Members that Update According to the Contents of a Single Cell
We start by creating a range of cells (cells E1-E6 in this example), that display the team members for any team name that is typed into cell G2. The steps to do this are:
- Type a team name into cell G2 (e.g. 'Admin' has been entered into the spreadsheet below);
-
Insert the following formula into cell E1:
=$G$2 -
Insert the following formula into cell E2:
=HLOOKUP($G$2, $A$1:$C$6, ROW(), 0) - Copy the formula in cell E2 into cells E3 - E6.
These formulas are displayed in the spreadsheet below:
Note that, in the above example spreadsheet, the formula "=$G$2" in cell E1 reflects team name that is entered in cell G2, and the formulas in cells E2 - E6 use the Hlookup and Row functions to look up the members of this team.
Step 2 - Create a Drop Down List Containing the Team Members for the Selected Team
In Step 1 above, we created a range of cells (cells E2-E6) that vary according to the team name that is typed into cell G2. We will now use the names in cells E2-E6 to create a drop down list that will automatically update every time the value of cell G2 is changed. We will put this drop-down list into cell G4 of the spreadsheet.
To do this:
- Select cell G4
-
Click on the Data Validation option within the 'Data Tools' grouping on the Data tab of the Excel ribbon, and from this drop-down menu, select the option Data Validation... (see right).
This will cause the 'Data Validation' dialog box to open up (see below).
-
Within the 'Data Validation' dialog box:
- In the 'Allow' input box, select the option 'List'. This should cause further options to appear in the dialog box.
- Ensure the option 'In-cell dropdown' is checked.
- 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").
- Click OK.
You will now have a drop-down menu in cell G4, that displays the team members relating to the team name that is currently typed into cell G2 (see below).
Typing a different team name into cell G2 causes the list in cell G4 to automatically update with the new team members.
Step 3 - Optional Extras
To make your spreadsheet look that bit more professional, you might prefer to hide columns A-C, which contain the original data, or place this data onto a different worksheet.
As a further finishing touch, you could also make the cell G2 into a drop-down list, from which a team name can be selected, as shown in the above spreadsheet on the right.