— The Excel file related to this article can be downloaded from Dependent Drop Down List 02 —
In Article 15, we saw that our Dependent Data list can grow / reduce vertically but we had fixed the horizontal range. Now, we will remove this restriction and our data list can grow / reduce vertically as well as horizontally.
1. Prepare the formula for first drop down.
In article 15, we saw that it is very simple i.e. $F$1:$K$1. Let's assume that maximum it can grow till Z1. In this case, we need to prepare a formula which will give us a range of $F$1:$K$1 within the range of $F$1:$Z$1 if South America is selected. If another entry is added or deleted, it should adjust appropriately.
The formula in this case would be –
=OFFSET($F$1,0,0,1,COUNTA($F$1:$Z$1))
(Though OFFSET is a volatile formula but simplicity of OFFSET is a reason to use)
2. Prepare the formula for second drop down
The formula was already there, only change is that $K is replaced by $Z. (As I said earlier 12 is a number till which the list can grow. You can put any number say 100, 900 etc.)
=OFFSET(INDEX($F$1:$Z$12,,MATCH($A$2,$F$1:$Z$1,0)),1,0,
COUNTA(INDEX($F$2:$Z$12,,MATCH($A$2,$F$1:$Z$1,0))))
3. Create the first drop down list and second drop down list utilizing above formulas.
Select A2 > Data tab > Data Validation > Data Validation > Choose List in Allow: and in source put
=OFFSET($F$1,0,0,1,COUNTA($F$1:$Z$1))
Select B2 > Data tab > Data Validation > Data Validation > Choose List in Allow: and in source put
=OFFSET(INDEX($F$1:$Z$12,,MATCH($A$2,$F$1:$Z$1,0)),1,0,
COUNTA(INDEX($F$2:$Z$12,,MATCH($A$2,$F$1:$Z$1,0))))
Now, your dynamic drop down list which can grow vertically as well as horizontally is ready.