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:
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.
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.
Click on the New button to add a new named range and it will open the New Name dialog box.
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:
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.
This will open the Data Validation dialog box.
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:
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.
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.
52 comentários:
Hi,
Instead of VLOOKUP(), you can also use CHOOSE and MATCH. This will eliminate the need of country list column
Hi, the easiest way is to use function INDIRECT, in data validation source.
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
How to use INDIRECT??
Here's an article about INDIRECT() function:
http://www.excel-user.com/2010/06/indirect-function.html
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?
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.
Not sure if it works with 2007 version. I've never worked with that version. On 2010 it works fine.
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.
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
@ 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
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
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?
That's an awesome Excel trick, I learnt today! :)
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!
how you do sumproduct with hose validation lists if they refer to other sheet?
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.
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
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
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
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.
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
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
Nice article, keep sharing
Forum.app
Jobs
Jedox
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
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
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
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
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
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
Great Article. Thank you for sharing! Really an awesome post for every one. oracle training in chennai
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
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
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
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
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
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
This is a great post. This post gives a truly quality information. I am certainly going to look into it. Really very helpful tips are supplied here. Thank you so much. Keep up the great works..
Data Science Training In Chennai
Data Science Online Training In Chennai
Data Science Training In Bangalore
Data Science Training In Hyderabad
Data Science Training In Coimbatore
Data Science Training
Data Science Online Training
Really it was an awesome article...very interesting to read..You have provided an nice article....Thanks for sharing..
Web Designing Training in Chennai
Web Designing Course in Chennai
Web Designing Training in Bangalore
Web Designing Course in Bangalore
Web Designing Training in Hyderabad
Web Designing Course in Hyderabad
Web Designing Training in Coimbatore
Web Designing Training
Web Designing Online Training
This is extremely helpful info!! Very good work. It is very interesting to learn and easy to understood. Thank you for giving information. Please let us know and more information get post to link.
IELTS Coaching in chennai
German Classes in Chennai
GRE Coaching Classes in Chennai
TOEFL Coaching in Chennai
spoken english classes in chennai | Communication training
I feel really happy to have seen your webpage.I am feeling grateful to read this.you gave a nice information for us.please updating more stuff content...keep up!!
Android Training in Chennai
Android Online Training in Chennai
Android Training in Bangalore
Android Training in Hyderabad
Android Training in Coimbatore
Android Training
Android Online Training
Wonderful post and more informative!keep sharing Like this!
acte chennai
acte complaints
acte reviews
acte trainer complaints
acte trainer reviews
acte velachery reviews complaints
acte tambaram reviews complaints
acte anna nagar reviews complaints
acte porur reviews complaints
acte omr reviews complaints
This is extremely helpful info!! Very good work. It is very interesting to learn and easy to understood. Thank you for giving information.
AWS Course in Chennai
AWS Course in Bangalore
AWS Course in Hyderabad
AWS Course in Coimbatore
AWS Course
AWS Certification Course
AWS Certification Training
AWS Online Training
AWS Training
I as well as an aspiring blog writer, but I’m still new to the whole thing. Do you have any recommendations for newbie blog writers? I’d appreciate it.
| Certification | Cyber Security Online Training Course | Ethical Hacking Training Course in Chennai | Certification | Ethical Hacking Online Training Course | CCNA Training Course in Chennai | Certification | CCNA Online Training Course | RPA Robotic Process Automation Training Course in Chennai | Certification | RPA Training Course Chennai | SEO Training in Chennai | Certification | SEO Online Training Course
Firstly talking about the Blog it is providing the great information providing by you . Thanks for that .Hope More articles from you . Next i want to share some information about Salesforce training in Banglore .
Firstly talking about the Blog it is providing the great information providing by you . Thanks for that .Hope More articles from you . Next i want to share some Information about Salesforce training in Hyderabad .
Without investing on any new software or hardware, it can provide you customized applications, to meet the requirements of it and provide you success in achieving your business goals. Salesforce interview questions and answers
Awesome articles, new exciting information are learned from your blog.
software testing techniques
what is the latest version of angularjs
what programming language is google developed in
ccna cloud
data science interview questions and answers for experienced
AngularJS is a structural framework for dynamic web apps. It lets you use HTML as your template language and lets you extend HTML's syntax to express your application's components clearly and succinctly.
tally training in chennai
hadoop training in chennai
sap training in chennai
oracle training in chennai
angular js training in chennai
Another great thing about Excel is the fact that it is such a versatile tool. In fact, it is because of this versatility that the act of reporting data via the many sub-operations that come with the processor here mesh very well with the production and the creation of the metrics to be used in plotting out the balanced scorecard. excel spreadsheet consulting
Post a Comment