Cascading data validation based on named ranges

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.

 

cascadingdatavalidation1

 

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

 

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

 

cascadingdatavalidation3

 

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

 

cascadingdatavalidation4

 

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

 

cascadingdatavalidation5

 

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.

 

cascadingdatavalidation6

 

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

 

cascadingdatavalidation7

 

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

 

cascadingdatavalidation8

 

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))

Leave a Reply

Your email address will not be published. Required fields are marked *