When you fill in an online form it is quite common that a choice of drop down options will be dependant on one of your previous answers. In this post we’ll look at how you can replicate that in Excel.

 

In this example, a firm has 8 offices across 3 different countries. When you have selected a country, you want only the relevant offices to show in the ‘Office’ drop down.

 

Firstly, create some lookups with the offices for each country.

 

 

Then go to the Name Manager and add names to each of the ranges.

 

Create a separate ‘CountryLookup’ for the first drop down box, with the options: China, UK and USA.

 

 

In the input form, use the following Data Validation on your Select Country input (Data tab – Data Validation).

 

 

Now add a name to the input for Country in cell C2, named ‘Country’.

 

 

You need to translate the ‘Country’ name into the range name of the Office lookup. You can do this in a separate cell which you can hide later on.

 

 

Then add some Data Validation to the Offices input which looks at this hidden cell, select List and input =INDIRECT(D1).

 

 

As this is looking up the ChinaLookup range, the drop down options available are Beijing and Shanghai.

 

 

It’s probably worth adding another range containing offices from all countries, in case no Country has been selected. If you name that as ‘AllOffices’, then the data validation formula needs to become:
=IF(Country = “”, AllOffices, INDIRECT(D1))