Taking the Data Validation Dropdown list to the next level

Creating a dependent or linked Dropdown list

In this article I will show you how to create dependent Dropdown lists using Data Validation and an Excel function called Indirect. First let’s take a look at what we are trying to accomplish. We will start with the following workbook and data…
 Now we wish to have two dropdown menus, one in cell C3 and one in cell C5. But, and here’s the catch, the dropdown in C5 has to be relative to whatever is chosen from the dropdown in C3. So if for instance someone picks France in C3, the dropdown menu in C5 has to give Paris, Lyon and Marseille, but if Slovenia is chosen in C3, Ljubljana, Maribor and Koper are in a dropdown in C5.
To achieve this we will use Data Validation with two additions. The first being the INDIRECT function and the second is using the named ranges.

Step 1: Dropdown list with Data Validation

Just for clarification I will explain how to create a dropdown list in cell C3 using basic Data Validation. So standing on C3 you go to Data/Data Validation
 In the dialog box you get you choose List under Allow: and point to the cells containing the names of the countries in the Source field.
 So now you get a dropdown list in cell C3 where you can choose any of the countries.

It’s very important to understand that with using Data Validation to create the dropdown, and accepting all defaults under Error Alert you have also effectively limited the input into that table to only the values from the dropdown.

Step 2: Creating the named ranges

Before we can define the dropdown in C5, we must create a few named ranges so that Indirect will point to the right range.
We want to name the range where the French Cities are France and so on. We will use Excel to do this for us. We go to Formulas/Create From Selection and choose Left Column. So create the named ranges based on the values in the first column of the data I selected.  
Doing this gives us six named ranges which we can see at the dropdown in the Name Box.
Pay extra attention to the United Kingdom name which was altered in way. The name reads United_Kingdom. The reason the underscore appeared is that names in Excel cannot include spaces. Therefor United Kingdom is not a valid name for Excel and it was changed. This will have dire consequences for our sample but it will be a learning experience. So now that we have the named ranges we need, we can go to the next step but since the next step will include the Indirect function we should first familiarize ourselves with that function.

INDIRECT function

Simplest sample is this. In A1 we have 100, in A2 we have A1. In A1 we write =INDIRECT(A2) and we get 100. So the cell that we give to the INDIRECT function actually tells the function where it should be looking. In effect, cell A2 told the INDIRECT function to go look into A1 and there it read the 100 it returned. Now we can really go to Step 3…

Step 3: creating a dependent dropdown

Same as in the above example, while standing on C5 we go to Data/Data Validation and say Allow: list. Now here the magic. Under Source: we write =INDIRECT($C$3) so we say to excel go and see what is written in C3. We already know it’s going to be a name of a Country. But with indirect we say go look at that name and see if there are any named ranges under that name. And INDIRECT tells the Data Validation which cells to take as a source and is effectively dependent on what is chosen in C3.
Now this works perfectly for all the countries except for UK, since cell C3 says United Kingdom, the named range for UK cities is under United_Kingdom (for reasons we discussed earlier) the INDIRECT function cannot work and produce a range for the dropdown.
See the attached sample Workbook
dropdowns.xlsx
In my next article I will be discussing the Named ranges or should I say Dynamic Named Ranges which can take this dropdown a level higher since it would be dynamic and you can add Cities and they dynamically appear in the Dropdown list.