Hola! Bienvenido! That’s hello and welcome in Spanish (J)
Coming
to the point, we have seen how to build cascading lists using IF function
and INDIRECT
function. There are couple of more ways you can do the same, so I thought to put it here. Let’s see one of those ways in this post. I will set the basic backdrop
using our example in earlier posts.
We
have country, region and state to be selected from the drop down lists and we
wanted these lists to be cascading / dependent on previous option selected.
In the same sheet, from A6 and
onwards I have the countries, regions and states list. Let’s arrange them as in
the table shown below. We are going to give numbers for each country and region
list. For example: For India, I gave number 1 and USA got 2. Same way I gave
numbers to regions list. You need not number State List. We will have to number
this way until last but one layer. We will see how these numbers will be used
going forward. Your data will look like below once done.
Select the first list values and give a name of your choice. In my
example above, I select A7:A8 and name it as CountryList (Refer this post on how to give names to ranges)
From the second list, select those options relating to first item in
first list and name it. In above example, I will select A12:A15 (regions
relating to first item in first list i.e. India) and name it as India.
From the second list, select those options relating to second item in
first list and name it. In above example, I will select A16:A19 (regions
relating to second item in first list i.e. USA) and name it as USA.
You will continue doing this until all items in second list are named
respectively. Once this is done, we will move on to third list items.
From the third list, select those options relating to first item in
second list and name it. In above example, I will select D7:D8 (regions
relating to first item in second list i.e. South India) and name it as SouthIndia.
From the third list, select those options relating to second item in
second list and name it using exact wordings as the second item in second list.
In above example, I will select D9:D10 (regions relating to first item in second list i.e. North India) and name it as NorthIndia.
Go on and name all other items following logic above. Once we are done
naming all the items in third list, our list of named ranges should look like
below. (I didn't name USA related items yet, but you have to in your real life
example)
Alright, we are done arranging our data. Let's move on to apply
validation in the cells.
In cell B1, apply validation as you normally do. That means, you will
select List in Data Validation dialogue box Settings tab Allow: field. In the Source, type =CountryList
In cell B2, you will select List in Data Validation dialogue box Settings tab Allow: field. In the Source
field, type the below formula!
=CHOOSE(VLOOKUP($B$1,$A$7:$B$8,2,0),India,USA)
In cell B3, you will select List in Data Validation dialogue box Settings tab Allow: field. In the Source
field, type the below formula!
=CHOOSE(VLOOKUP($B$2,$A$12:$B$19,2,0),SouthIndia,NorthIndia,EastIndia,WestIndia)
Go ahead and check the drop-down lists. Are they working like cascading
lists?
Let’s see what our functions are doing in cell B2.
VLOOKUP function looks for value of B1 in table A7:B8 and returns a
number from 2nd column (that is where we gave numbers). Result of
this is 1 if you select India in B1 and 2 if you select USA. CHOOSE function
will select named ranges India or USA based on the VLOOKUP result. Named Range India will be selected if VLOOKUP
result is 1 and Name USA is selected
if VLOOKUP result is 2. Same logic for other lists as well.
You can download workbook I used for this example by clicking here.
There is one more way we can get the cascading lists i.e. using INDEX
function. I will write about that in next post. Stay tuned!
