—- Excel file related to this article can be downloaded from Dependent Drop Down List —-
In this article, I want to create a dynamic dependent data list. It means that entries can be added, removed at any point of time without impacting your Dependent Data List. Without making matter complicated, this article assumes that first level is fixed and second level can keep changing. In second article of this series, I am going to remove this dependency also, hence both first and second level of entries can be removed / added in second article. The examples which I am going to show is one level of dependency but same logic can be extended to any level of dependencies.
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.
2. Create the first drop down
Let's assume that cell A1 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 the formula for second drop down
Now, it can be simplified to finding the range which contains first drop down value in first row. For example, if I select South America, I should be able to get the range $K$2:$K$12 (I have taken $K$12 for illustration purpose only. You can have any range. The trick will be to show only the values from $K$2:$K$6 in case of South America, $H$2:$H$5 in case of Australia and so on)
The range can be located by INDEX – MATCH combination as shown below. Notice, there are two commas after $K$12 which indicates that all rows should be selected for the column in the range $F$2:$K$12
=INDEX($F$2:$K$12,,MATCH($A$2,$F$1:$K$1,0))
This formula gives a range of $K$2:$K$12 for South America.
4. Create the second drop down
Let's assume that we want to create the second drop down in B1.
Select B1 > Data tab > Data Validation > Data Validation > Choose List in Allow: and in source put
=INDEX($F$2:$K$12,,MATCH($A$2,$F$1:$K$1,0))
Now, select a continent name in A1 and you will have list of countries shown in dependent drop down.
5. Remove the blanks from second dropdown
We notice that there are blanks in second drop down at the end of the list. We need to remove these.
The logic, we will adopt is this – We have got the range in step 3. Now we need to count the non blank cells in that range and use OFFSET to get the required range.
=OFFSET(INDEX($F$1:$K$12,,MATCH($A$2,$F$1:$K$1,0)),1,0,
COUNTA(INDEX($F$2:$K$12,,MATCH($A$2,$F$1:$K$1,0))))
Note – INDEX($F$1:$K$12,,MATCH($A$2,$F$1:$K$1,0)) is first part of OFFSET and part within COUNTA making matter very simple. Hence, the formula is OFFSET("index formula",1,0,COUNTA("index formula")). In first INDEX, in place of $F$1:$K$12 we can put $F$1:$K$1 also. But as I said, I want to keep uniformity and make matter simple by having a very simple formula OFFSET("index formula",1,0,counta("index formula")).
INDEX($F$1:$K$12,,MATCH($A$2,$F$1:$K$1,0)) – Gives $K$1 in case of South America, $G$1 in case of Asia and so on. But, our range needs to start from second row i.e. $K$2 in case of South America, $G$2 in case of Asia and so on. Notice 1 in bold within OFFSET, it shifts the reference by 1 row. Hence, it makes $K$1 to $K$2 and so on.
COUNTA(INDEX($F$2:$K$12,,MATCH($A$2,$F$1:$K$1,0))) – Gives number of non blank cells. In case of Asia, it is 6. Hence, our reference becomes $G$2:$G$6.
6. Put the above formula in Dependent Data List.
Select B2 > Data tab > Data Validation > Data Validation > Choose List in Allow: and in source put
=OFFSET(INDEX($F$1:$K$12,,MATCH($A$2,$F$1:$K$1,0)),1,
0,COUNTA(INDEX($F$2:$K$12,,MATCH($A$2,$F$1:$K$1,0))))
Now, you have perfect looking dependent data list.
