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

Initially, you 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 can be inserted into cells E2 - E6 to make these cells show the members of the selected team.

In the example above, the following formula, which combines the HLOOKUP function and the ROW function, would produce the figures for the 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 formula in cell E2 is used to display the first member of the selected team. If the formula in cell E2 is copied into each of the cells E3 - E6, then the range E2 - E6 will vary according to the team name entered into cell $G$2.

A drop down list, built on the values in cells E2 - E6 will therefore also depend on the value of the cell G2. To create a drop-down list in cell G4, from these values,

1. Select cell G4
   
   
2. In Excel 2003: Select the Data Menu from the top of your Excel spreadsheet and from within this menu, select the option Validation...
   
   OR
   
   In Excel 2007: Select the Data Tab from the top of your Excel spreadsheet and from within this menu, select the option Data Validation→Data Validation...
   
   
3. The Validation menu will appear. Within this menu :
   
   

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.