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.

38 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?

Unknown 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.

Kayal said...

Such a nice post! I like this different content and This is a very good thing. Thank you...!

Oracle Training in Chennai
Oracle Training institute in chennai
Excel Training in Chennai
Corporate Training in Chennai
Embedded System Course Chennai
Tableau Training in Chennai
Linux Training in Chennai
Oracle Training in Chennai
Oracle Training institute in chennai

Reshma said...


Wonderful post and more informative!keep sharing Like this!
Tally Course in Chennai
Tally Course in Hyderabad
Tally training coimbatore
Tally Course in Coimbatore
Tally course in madurai
Tally Training in Chennai
Tally Institute in Chennai
Tally Training Institute in Chennai
Selenium Training in Bangalore
Ethical hacking course in bangalore

sasi said...

It's a looks very awesome article! Thanks a lot of sharing for information.
Python course in Chennai
Python Training in Bangalore
Python Course in Coimbatore
Python Training in Chennai
Big Data Training in Coimbatore
Salesforce Training in Bangalore
Python Classes in Bangalore
python training in hyderabad

greatrockdev said...

Howdy! I simply wish to offer you a big thumbs up for your great info you have got here on this post. I'll be returning to your blog for more soon.
Click here to getMoreinformation.

Unknown said...


python course in coimbatore
java course in coimbatore
python training in coimbatore
java training in coimbatore
php course in coimbatore
php training in coimbatore
android course in coimbatore
android training in coimbatore
datascience course in coimbatore
datascience training in coimbatore
ethical hacking course in coimbatore
ethical hacking training in coimbatore
artificial intelligence course in coimbatore
artificial intelligence training in coimbatore
digital marketing course in coimbatore
digital marketing training in coimbatore
embedded system course in coimbatore
embedded system training in coimbatore

Indhu said...

Thanks for sharing this informations.
hadoop training in coimbatore

C and C++ training in coimbatore

embedded training institute in coimbatore

RPA Course in coimbatore

Robotic process automation training in coimbatore

Blue prism training in coimbatore

Ui Path training in coimbatore

Anonymous said...

Nice article, keep sharing
Forum.app
Jobs
Jedox

akshaya said...

You deserve a great applause for your first rate work, which supports the readers with fantastic ideas. I take a bow for your extraordinary write-up.
Web Designing Course Training in Chennai | Web Designing Course Training in annanagar | Web Designing Course Training in omr | Web Designing Course Training in porur | Web Designing Course Training in tambaram | Web Designing Course Training in velachery


sathishkumar said...

python training in bangalore | python online training
aws training in bangalore | aws online training
artificial intelligence training in bangalore | artificial intelligence online training
machine learning training in bangalore | machine learning online training
blockchain training in bangalore | blockchain online training
uipath training in bangalore | uipath online training





surya said...

Thank you to the perform as well as discuss anything incredibly important in my opinion
angular js training in chennai

angular training in chennai

angular js online training in chennai

angular js training in bangalore

angular js training in hyderabad

angular js training in coimbatore

angular js training

angular js online training

sathya said...

Thanks for this informative blog please keep posting more often as it might help someone who is looking to gain more knowledge.

selenium training in chennai

selenium training in chennai

selenium online training in chennai

selenium training in bangalore

selenium training in hyderabad

selenium training in coimbatore

selenium online training

lavanya said...

I’ve found extensive lists before, but none this informative. Thanks for sharing!
Thanks and Regards,
amazing post written ... It shows your effort and dedication. Thanks for share such a nice post.Java training in Chennai

Java Online training in Chennai

Java Course in Chennai

Best JAVA Training Institutes in Chennai

Java training in Bangalore

Java training in Hyderabad

Java Training in Coimbatore

Java Training

Java Online Training


anand said...

nice post
Software Testing Training in Chennai | Certification | Online
Courses



Software Testing Training in Chennai

Software Testing Online Training in Chennai

Software Testing Courses in Chennai

Software Testing Training in Bangalore

Software Testing Training in Hyderabad

Software Testing Training in Coimbatore

Software Testing Training

Software Testing Online Training

Devi said...

Great Article. Thank you for sharing! Really an awesome post for every one. oracle training in chennai

meritstep Technology said...

Thanks for Sharing This Article.It is very so much valuable content. I hope these Commenting lists will help to my website
blockchain online training
best blockchain online training
top blockchain online training

ramesh said...


Very nice post here and thanks for it .I always like and such a super contents of these post.Excellent and very cool idea and great content of different kinds of the valuable information's.

Azure Training in Chennai

Azure Training in Bangalore

Azure Training in Hyderabad

Azure Training in Pune

Azure Training | microsoft azure certification | Azure Online Training Course

Azure Online Training

ramesh said...

Thanks for sharing such an informative blog. I have read your blog and I gathered some needful information from your post. Keep update your blog. Awaiting for your next update.

Azure Training in Chennai

Azure Training in Bangalore

Azure Training in Hyderabad

Azure Training in Pune

Azure Training | microsoft azure certification | Azure Online Training Course

Azure Online Training

aravind said...

Thanks for Sharing This Article.It is very so much valuable content."Nice blog I really appreciate your words,Nice post. It is really amazing and helpful.
DevOps Training in Chennai

DevOps Online Training in Chennai

DevOps Training in Bangalore

DevOps Training in Hyderabad

DevOps Training in Coimbatore

DevOps Training

DevOps Online Training

dhinesh said...

Thanks for sharing this wonderful content.its very useful to us.This is incredible,I feel really happy to have seen your webpage.I gained many unknown information, the way you have clearly explained is really fantastic.keep posting such useful information.
Full Stack Training in Chennai | Certification | Online Training Course
Full Stack Training in Bangalore | Certification | Online Training Course

Full Stack Training in Hyderabad | Certification | Online Training Course
Full Stack Developer Training in Chennai | Mean Stack Developer Training in Chennai
Full Stack Training

Full Stack Online Training



Rashika said...

thanks for sharing such a nice info.I hope you will share more information like this. please keep on sharing!

Digital Marketing Training in Chennai

Digital Marketing Course in Chennai

SEO Training in Chennai

Digital Marketing Training in Bangalore

Digital Marketing Training in Hyderabad

Digital Marketing Training in Coimbatore

Digital Marketing Training

Digital Marketing Course

Digital Marketing Online Training