Create conditional drop-down lists
Today’s author is Reinout Dorreboom from the Netherlands, a Technical Consultant at Getronics, where he has worked with Office applications for many years, and where his Microsoft Certified Training skills enabled him to help other people get up to speed with Excel.
In Excel 2007 (and earlier), it’s possible to create a drop-down list. By using the INDIRECT function, you can then create additional drop-down lists that are conditional to the first drop-down list.
In this example we’ll create the following table:
| | A | B | C | D |
| 1 | | Country | City | Street |
| 2 | David | Netherlands | Rotterdam | Abraham van Stolkweg |
| 3 | Linda | Germany | Munich | MunichStreet1 |
| 4 | Peter | Britain | London | Bacon Street (E1) |
Columns B, C, and D contain drop-down lists. If you select Netherlands in the drop-down list in column B, only Dutch cities will be displayed in the drop-down list in Column C. If you select Germany, only German cities will be displayed in the drop-down list in Column C. Similarly, when you select a city in Column C, only the street names of that city will be displayed in column D.
To create conditional lists, complete the following procedures.
Enter the data
First, we’ll create the table as shown below.
Now, we’ll create a few lists.
First, we’ll create a list of countries. In this example, we create the following countries:
- In Cell F1, type Netherlands.
- In Cell G1, type Britain.
- In Cell H1, type Germany.
As you can see I made a horizontal list instead of a vertical list. This isn’t really necessary, but it is easier to maintain to keep the cities under the right country.
Second, we’ll add three cities to pick from for each country.
| Cell F2: Amsterdam | Cell G2: London | Cell H2: Bonn |
| Cell F3: Rotterdam | Cell G3: Canterbury | Cell H3: Berlin |
| Cell F4: Eindhoven | Cell G4: Manchester | Cell H4: Munich |
Now, we’ll create a list of streets for each city. In the example, you can then choose from two streets per city.
| Cell F8: AmsterdamStreet1 | Cell G8: LondonStreet1 | Cell H8: BonnStreet1 |
| Cell F9: AmsterdamStreet2 | Cell G9: LondonStreet2 | Cell H9: BonnStreet2 |
| Cell F11: RotterdamStreet1 | Cell G11: CanterburyStreet1 | Cell H11: BerlinStreet1 |
| Cell F12: RotterdamStreet2 | Cell G12: CanterburyStreet2 | Cell H12: BerlinStreet2 |
| Cell F14: EindhovenStreet1 | Cell G14: ManchesterStreet1 | Cell H14: MunichStreet1 |
| Cell F15: EindhovenStreet2 | Cell G15: ManchesterStreet2 | Cell H15: MunichStreet2 |
The worksheet should look like this now:
Define the names
Ok, all content is provided. Now we can start creating a name for each range.
- Select the cells F2:F4 and name the range: Netherlands
- Select the cells G2:G4 and name the range: Britain
- Select the cells H2:H4 and name the range: Germany
- Select the cells F7:F8 and name the range: Amsterdam
- Select the cells F10:F11 and name the range: Rotterdam
- Select the cells F13:F14 and name the range: Eindhoven
- Select the cells G7:G8 and name the range: London
- Select the cells G10:G11 and name the range: Canterbury
- Select the cells G13:G14 and name the range: Manchester
- Select the cells H7:H8 and name the range: Bonn
- Select the cells H10:H11 and name the range: Berlin
- Select the cells H13:H14 and name the range: Munich
For information about how to define names, see Define and use names in formulas.
Create the drop-down lists
After defining the names, we can create the drop-down lists.
First, we’ll make a drop-down list for Country.
1. Select cell B2.
2. On the ribbon, click the Data tab.
3. In the Data Tools group, click Data Validation.
4. Fill the values as follows:
In the Allow box, select List.
In the Source box, type =$F$1:$H$1
Note: Dollar signs ($) are used in the formula so that we can drag the cell downwards from David to Peter.
5. Click OK.
6. Drag the cell content downwards.
Now, we’ll make the first conditional drop-down list.
1. Select Cell C1.
2. On the ribbon, click the Data tab.
3. In the Data Tools group, click Data Validation.
4. Fill the values as follows:
In the
Allow box, select
List.
In the
Source box, type
=INDIRECT($B2)
Note: In the formula, a Dollar sign ($) is not placed before the row. That is because we want to drag the cell formula downwards. 5. Click OK.
Note: If you click OK, you may get an error message indication that the evaluation of the formula was an error. This is correct, because the cell where this list depends on (B2) is empty.
6. Drag the cell content downwards.
Now, we’ll make our second conditional drop-down list.
1. Select cell D2.
2. On the ribbon, click the Data tab.
3. In the Data Tools group, click Data Validation.
4. Fill the values as follows:
In the
Allow box, select
List.
In the
Source box, type
=INDIRECT($C2)
Note: In the formula, a Dollar sign ($) is not placed before the row. That is because we want to drag the cell formula downwards. 5. Click OK.
Note: If you click OK, you may get an error message indicating that the evaluation of the formula was an error. This is correct, because the cell on which this list depends (C2) is empty.
![clip_image012[1]](http://blogs.msdn.com/blogfiles/excel/WindowsLiveWriter/Createconditionaldropdownlists_9344/clip_image012%5B1%5D_thumb.jpg "clip_image012[1]")
6. Drag the cell content downwards.
Done!
If you choose a country, the City field displays only cities for that country. And only streets for the selected city will be shown.
