Cascading Validation Lists

Here’s the scenario: on our worksheet, we want to choose a Country and the corresponding City. We want to limit the countries available to the user as well as the cities that they can choose. For start, we want to choose the country and on the second cell we can only put cities that correspond to that country. It makes sense, right?
So, let’s start we our simple example on how we can make cascading (or dependent) Validation Lists. In cell A2 we want to put the country and on cell B2 we want to put the city, like this example:
Cascading-Validation-Lists1

To start, we have to build our “database”. On a second sheet, I’ve made a list of countries that I want to have available for the user to choose on my validation list and I putted and index number that will correspond to the right cities list. So, for instance, for France, I used the index 2 that will be corresponding to the cities list 2. You will see after how this index number will be used.

Cascading-Validation-Lists2

Now, if you’re using Excel 2010, you can have the “source” lists on a separated sheet but if you’re using Excel 2003 for instance, you can only have the values on a second sheet if you use named ranges. We will use the name ranges so that this cascading validation lists work on both versions. So, the next step is to create the named ranges for our lists. On your Formulas tab, we have the Name Manager button. When you click it, it will open the Name Manager dialog box.

Cascading-Validation-Lists3

Click on the New button to add a new named range and it will open the New Name dialog box.
 
Cascading-Validation-Lists4

On the Name field you can put, for instance, “Country” for our first named range and on the “Refers to” field we will select, in this case, =Sheet3!$A$3:$A$5 to get the list of countries. Click OK to finish. Then we need to create the named ranges for the rest of the lists. Click again on the New button on the Name Manager dialog box and repeat the process to create three more named ranges: England, France and Portugal, selecting for the “Refers to” field the corresponding range of cities names. This will leaves with 4 named ranges, like this:

Cascading-Validation-Lists5

Now we can build our validations lists on our original sheet where we want to select the data from our “database”. With cell B1 selected (where we want to select our country), go to the Data tab and click on Data Validation and choose Data Validation.

Cascading-Validation-Lists6

This will open the Data Validation dialog box.

Cascading-Validation-Lists7

Now we want to use a list of options for the user to select so go ahead and on “Allow” change the value to “List”. This will open the Source field that is where we will put our Country named range. So put =Country and click OK. Now we want to make the second Validation List for the user to choose the city. Select, in this case, cell B2 that is where we will put this second validation list. Now comes the trick. We need to check the value of the country cell (cell B1) to get the index of the corresponding value on our “database”. If the user selected Portugal we need to get the index “3” that will tell us to get the list of cities that correspond to Portugal. For that we will make a VLOOKUP of the value in cell B1 in the Countries table. After we get the index value, we will “choose” the list to use for the Source of our second validation list. To make this we will use this formula:

=CHOOSE(VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),England,France,Portugal)

So, what does this formula do? It will start for looking up the value on cell B1 against the values on our countries table and getting the corresponding index that it will use on the CHOOSE() function to get either the 1st, 2nd or 3rd named range.

This is how our second validation list will look:

Cascading-Validation-Lists8

The result will be two cascading or dependent Validation Lists. If we choose country France, we will have on the cities Validation List the cities from France only.

Cascading-Validation-Lists9

This article demonstrates how you can make basic cascading Validation Lists to use on your sheets. You can take this example and use it on your own real life situations.

18 comentários:

Parin Thacker said...

Hi,

Instead of VLOOKUP(), you can also use CHOOSE and MATCH. This will eliminate the need of country list column

Anonymous said...

Hi, the easiest way is to use function INDIRECT, in data validation source.

Mukund said...

Hi

I tried with this option but its useful for only MS Office 2010, it doesn't work in MS Office 2003.

Why is it so ?

Kindly reply me on my id mukundlan@gmail.com

Lars Wikman said...

How to use INDIRECT??

jppinto said...

Here's an article about INDIRECT() function:

http://www.excel-user.com/2010/06/indirect-function.html

Jina said...

I'm using 2007 and the formula does not work if I put the data in another sheet. I tried transferring to the same sheet but still didn't work.

Is it only works for 2010 version?

nur fuad said...

Hi Mr. The formula used in validation doesn't work in excel 2007 because the lookup value refer to another sheet.
I modify the lookup formula refer to named range that refer to another sheet. And It's works.
Thanks.

jppinto said...

Not sure if it works with 2007 version. I've never worked with that version. On 2010 it works fine.

Miguel Sousa said...

Hi JP Pinto, I tried this with Excel 2007 and it didn´t work at first, because like you said we need to name the ranges. So I changed "Sheet3!$A$3:$B$5" for the name I choose and it worked. Thanks for your lessons and tips. I only knew pplware for 2 weeks ago and I find the Sunday rubric very useful. I'll try to send a post one of these days.

jppinto said...

Hello Miguel,

Glad that you liked the Pplware articles. Please take a look also to the "Programar" magazine where I've written an article about "Microsoft PowerPivot as a BI tool" here: http://www.portugal-a-programar.org/revista-programar/edicoes/download.php?e=34&t=site

Subba said...

@ Lars Wikman,

in the place of =choose........ function in datavalidation, use =indirect(b1)

This will replace the range of cell named with countries.

Reg
Subba

Kees A said...

Already several years ago I placed on my website an intermediate solution that is, in my view, a lot more flexible than the one shown here. The comments are in Dutch. However, if wanted I can translate these into English. In mean time I have made even better solutions. See http://www.keesampt.com/Service.html and there the file Keuzelijsten_op_2-niveaus.xls

Anonymous said...

Is there a way to get the city to go back to blank if the country changes, or is removed>? Would you just be able to use an IF statement in the beginning?

Karthik said...

That's an awesome Excel trick, I learnt today! :)

Karthik said...
This comment has been removed by the author.
Barneswa said...

This is really helpful, I would consider myself a basic user of excel and I've managed to set up a set of cascading lists with three levels, that is also compatible with 2007. I found that when referring to the named Thanks!

Anonymous said...

how you do sumproduct with hose validation lists if they refer to other sheet?

Wendell Bolar said...

We use this strict validation for our reports and data input via excel for our small business software. We need to define variables clearly for it to work.