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. The following example shows you how to create a drop-down list containing the staff members for a selected team. This list varies depending on which team the user is interested in.
For this example, we begin with a list of members of each team, as shown below:
We will first range of cells that will show the team members for any selected team. The team name is typed into cell G2, and a formula in cells E2-E6 returns the members of the selected team.
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.
We can now create a drop down list, that takes its possible values from cells E2-E6. Therefore, the contents of this drop down list will also depend on the value of cell G2.
To create a drop-down list in cell G4, from the values in cell E2-E6:
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.
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.