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.