Sometimes in Excel you want to make a selection dependent on another choice. For instance, you would like to select a country from a list specific to a region. Thus the user first selects a region, and then only countries from that region are presented in the following drop-down.
To do this, you need to use Data Validation rules, the INDIRECT function and specifically named ranges.
Using the listings of countries available on the internet, for instance, here, we can build a list of countries by region.
First, we enter the data that will be in the first drop-down box, in this case, the regions. Then these entries are grouped under one name.
To name this range, select all the regions and type in the name “regions” into the address box at the top left and hit return.
Then do the same for each list of countries, naming each the same as one of the possible selections in the 1st drop-down box (i.e. one of the regions)
Now that the data is all in place, we can set up the drop-down boxes. To do this, we use data validation to present a list.
Cell A2 has the data validation set to
· List
· =regions
Cell B2 has the data validation set to
· List
· =indirect(a2)
Now whatever is entered in the “Regions” drop-down listing will drive the choices offered in the Country drop-down.
It is important to note that the choices offered in the 1st listing can not have spaces in (as named ranges are not allowed to have spaces in). If it is necessary to have spaces in the 1st choice, this restriction can be circumvented by using a Vlookup within the indirect.
To do this, you need to set up an additional range where the names in the 1st choice are linked to the named ranges that don’t have spaces.
