The following example shows you how to use Excel formulas to create a variable drop-down list.
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.
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:
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.
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:
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).
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.
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.