— The Excel files related to this article can be downloaded from Dependent Drop Down List_Single Words & Dependent Drop Down List_Multiple Words —
Creation of dependent data list is a topic which I wanted to write and I have got some time to talk about this. In this article, I will be covering the dependency for one level but same logic can be extended to more levels.
1. Create the right layout for your dependent data list
Suppose, you have a list of 6 continents and you want to display the countries on the basis of continents selected. The step 1 is to layout your data properly.
Below, is the layout which you need to use. First row will contain the name of continents (i.e. first level drop down values) and below them will be the list of countries (i.e. second level of drop down values)
No blanks are allowed in between the list entries. Blanks can be only at the end.
(Please note that Row1 has single words like North America is written as NorthAmerica i.e. no blanks. Also, it doesn't have any special character like @, % etc. The reason is that Naming Range doesn't support blanks / special characters in between the words. For matters of simplicity, let's keep it as one word without any special characters. We will remove this limitation later on)
2. Create the first drop down
Let's assume that cell A2 needs to contain the first drop down.
Select A2 > Data tab > Data Validation > Data Validation > Choose List in Allow: and in source put $F$1:$K$1
3. Prepare for second drop down – Create Name Ranges
We can see column F has values from F1 till F7.
3.1 Select the range F1:F7
3.2 Formulas tab > Create from Selection > The Create from Selection box will have Top Row automatically selected and all others deselected > Click OK
3.3 Now, you have created a named range Africa.
3.4 Do, it for all other ranges also i.e. select G1:G7 for named range Asia and repeat steps 3.1 to 3.3. For Australia H1:H5 and repeat steps 3.1 to 3.3. Do it for all the continents.
4. Create your second drop down
Select B2 > Data tab > Data Validation > Data Validation > Choose List in Allow: and in source put
=INDIRECT($A$2)
Now, you have the drop down created.
Removing Limitation of Spaces (blanks) in First Row (First Dropdown)
We talked about the limitation that First Row (First Dropdown) can not contains spaces between the words as Name Manager doesn't support space in the Name Box. Let's now remove this.
Let's assume you have a list like below where first row has blanks – Notice Australia and NZ, North America & South America.
Your remember in Step 3, you created Named Ranges. Excel automatically replaces blanks with underscore and creates named ranges. Hence, you will have Australia_and_NZ, North_America & South_America if there are blanks.
The only change you need to make is for step 4. The formula in this case would be
=INDIRECT(SUBSTITUTE($A$2," ","_"))
Now, you have the drop down created even with blanks in first row.
Removing Limitation of Illegal Characters in First Row (First Dropdown)
Many characters are not allowed in the name and when you follow step 1, Excel automatically replaces them. Hence, if you put Australia & NZ, the name range created will be Australia___NZ (2 underscores for spaces and 1 for &). Excel treats & as illegal character as & is a concatenate operator.
Similarily, if I put C1, Excel will treat it as illegal character while creating name as C1 is also a cell address. Hence, Excel creates a named range like C1_ .
For this case, don't use "Create from Selection" method to create Named Ranges
Create the named ranges by selecting the range and choosing Define Name rather from Create From Selection. As you will have to use an INDIRECT formula as given in Removing Limitation of Spaces (blanks) in First Row (First Dropdown). You can use appropriate SUBSTITUTE, REPLACE or LOOKUPS to workout replacing the characters.