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. In the following example a drop-down list is created, which contains all the staff members for a selected team. This list varies depending on which team the user is interested in.
We begin with a list of members of each team, as shown below:
Next, we create a range of cells that 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.
We can now create a drop down list, that takes its valid values from cells E2-E6.
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, as shown below.