Data Validation - Cascading Lists (using IF function)

We have been seeing a series of posts on Data Validation in our earlier posts. Today, let’s see another post on the same topic with an example on how to use dependent lists in data validation.

Here is a situation. I have an Excel template designed to capture personal details of candidates attending interview in my office. I have couple of cells with drop down lists to get location of the candidate. I also have a database of the countries, regions and states somewhere in the same sheet. Based on what we learnt in previous posts, we can do fairly well in creating a validation with these lists. Here is my template (A1 to B3) and database below it. To keep the example simple and presentable, I only took 2 countries and few states. I will leave it to you to imagine complex data.

See how the options in drop downs appear once I apply validation.

However, no matter what country I select in cell B1, list of regions available in cell B2 will be same. Same with B3, list of states will appear same. Now think of a user by mistake selects India as country and Alabama as the state. It’s Possible, right?

Think of it, would it not be better if I select India in cell B1, B2 list will be changed to have only regions that are related to India, and based on B2 selection, B3 drop down changes automatically to show only related options? Of course, it will be very nice. It will not only appear neat and tidy, it also eliminates the incorrect combinations that user might select. This is what I call Dependent Lists or Cascading Lists. Fortunately, Excel supports this and we are going to see how.

Cascading Lists are possible in at least couple of ways depending on the context and complexity of data. Let’s see one of the options for fairly simple data as in above example.

Using IF function to select multiple ranges

Cell B1 validation will be same as how you do normally. It has 2 options i.e. India and USA.

=$A$7:$A$8

We know that we can enter a formula for source in List option under Data Validation. We are going to use a formula to select either Indian Regions or USA regions. In Cell B2 validation, enter the following formula.

=IF($B$1="India",$B$7:$B$10,$B$11:$B$14)

If user chooses ‘India’ in cell B1, then the dropdown source is taken as B7:B10 which is where I have Indian regions. Else, B11:B14 where we have USA regions. Same logic for cell B3, we will enter the below formula to have change source as per B1.

=IF($B$1="India",$C$7:$C$13,$C$14:$C$23)

I have made B3 data dependent on B1 option. You can modify this to be dependent on cell B2. Since we have only 2 countries in B1, one IF function is enough. However if you want to to make B3 dependent on B2, since there are 4 regions options for each country, you will have to nest multiple IF functions. I am assuming 2 states in order under each region in the below example, meaning Andhra & Arunachal under South India and so on. Mind you, geographically this is not correct. Formula in data validation will be as below, note the nesting of IF functions.

=IF($B$2="South India",$C$7:$C$8,IF($B$2="North India",$C$9:$C$10,IF($B$2="East India",$C$11:$C$12,$C$13)))

You can simplify the appearance of this formula a bit by using Named Ranges. Refer to our earlier post on how to name ranges.

I will name both countries together as CList and Indian regions as IndList and USA regions as USAList. Also, I will name the states under each region like Andhra & Arunachal as SIndList, Assam & Bihar as NIndList and so on so forth. Below is how my formulas look like.

In Cell B1: =CountryList

In Cell B2: =IF($B$1="India”,IndList,USAList)

In Cell B3: = IF($B$2="South India",SIndList,IF($B$2="North India”,NIndList,IF($B$2="East India",EIndList,WIndList)))

After entering above formulas, see how the drop downs appear, once I select respective options.

I will be the first one to admit, using IF function will be very tedious if you have complex data. In the nextpost we shall see one more way to achieve the same but looking less messy. Stay tuned!!