Data Validation - Cascading Lists (using INDIRECT function)

We have discussed on how to use cascading lists in our earlier post. We also saw using IF function will be messy when you have multiple options under drop down menus. I promised that we will look into less messy options in this post, so let's go ahead and see about that.

As the title of this post suggests, this method includes using of INDIRECT function. For this to work, we need to first arrange our data in a structured form. We will be using the Named Ranges extensively. Let's quickly recap our example from earlier post and what we already know about INDIRECT function.

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. We also know that INDIRECT function will literally evaluate the reference address given as an argument. Okay, let's start arranging our data.

Define Named Ranges for your data

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 using exact wordings as the first item in first list. In above example, I will select B7:B10 (regions relating to first item in first list i.e. India) and name it as India. Remember, you have to use the exact name.

From the second list, select those options relating to second item in first list and name it using exact wordings as the second item in first list. In above example, I will select B11:B14 (regions relating to second item in first list i.e. USA) and name it as USA. Remember, you have to use the exact name.

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 using exact wordings as the first item in second list. In above example, I will select C7:C8 (regions relating to first item in first list i.e. South India) and name it as SouthIndia. Remember, you have to use the exact name but since Named Range does not allow a space in it, I will have to remove space and name it. We will see below how this can work. But remember again, apart from space in between, entire name should be exactly same.

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 C9:C11 (regions relating to first item in first list i.e. North India) and name it as NorthIndia. Remember, you have to use the exact name but since Named Range does not allow a space in it, I will have to remove space and name it. But remember again, apart from space in between, entire name should be exactly same.

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.

Apply Validation

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, type =Indirect(B1). In my example above, I do not have spaces for list items in first list. So the names given for second list items are exactly same as first list items. However situation may not be same for all. If you have spaces in first list items (like Republic of India), you would have named second list items as RepublicOfIndia (without spaces). In this case, you will have to type =Indirect(Substitute(B1," ","")). This formula replaces all spaces with nothing so your name and list value will be exactly same. We are going to use the same logic in our third validation. If while doing this, cell B1 value is not selected already, you may get an error saying this formula evaluates into error as in below picture. If your formula is correct, you can safely click Yes.

Error screen if B1 is empty

In cell B3, you will select List in Data Validation dialogue box Settings tab Allow: field. In the Source, type =Indirect(B2). We have spaces in second list items (example: South India), so we have named  third list item as SouthIndia (without spaces). Going by above logic, we will have to type =Indirect(Substitute(B2," ","")). This formula replaces all spaces with nothing so your name and list value will be exactly same. If while doing this, cell B2 value is not selected already, you may get an error saying this formula evaluates into error as above. If your formula is correct, you can safely click Yes.

Done. Check the validation in the cells. You must have got cascading lists as below.

This method is less cumbersome and very effective than using IF function. Likewise, you can use  SUBSTITUTE function to remove any additional characters in the list items to exactly match with the Named Range names.

A potential problem with cascading lists is that if first option is not selected then the second validation formula results into an error (refer to error screen above) and drop down will not work until one of the option in first validation is selected. Though sometimes this may be what exactly you want, in this case validation will not restrict user entering anything he/she wish in second validation.

Hope this post helped you understanding this concept better. Remember, learning in Excel is mostly about doing trial and error. Try it yourself and surely, you will be amazed with what you discover. Enjoy learning!!