Dear Forum members,
In one sheet I have a two column table with countries and cities.
I another sheet I am booking data and would like to select in one field the country from a validation drop down list.
Based on the selection of the country I would like in the next field to the right to select the city from a validation drop down list. The drop down list should display only the cities that are in that country.
Example:
COUNTRY CITY
France Paris
France Lyon
Italy Rome
Italy Milan
Australia Sidney
Australia Melbourne
etc.
When selecting "France" from the dropdown (lets say in cell A2) then I should get in the drop down of cell B2 only the cities "Paris" and "Lyon" and not the whole list of cities.
I thought that is a very common situation but I am struggling with that since a week and did not find the answer on internet.
Does anybody have a solution ?
Many thanks,
Olivier
Hello Olivier,
See http://www.contextures.com/xlDataVal02.html for how to make a dependant drop down list.
regards, Henk.
Hi Olivier,
There are two ways to achieve dependent data validation lists:
1. The easy way (similar/same as Contextures): https://www.myonlinetraininghub.com/excel-data-validation-with-dependent-lists
2. The more complex way but incredibly slick and completely dynamic: https://www.myonlinetraininghub.com/excel-factor-19-dynamic-dependent-data-validation
Mynda
Many thanks All,
I have found the link to Contexture before but I don't think this answers my case.
The way that is described by Contexture would force me to have as many columns in my data sheet as number of countries (1 list for each country). Imaging there are 160 countries (and this is just an example because my case is actually >10000 customers with 5 to 10 contracts).
We could even have additional dimensions like street names, then the proposed solution does definitely not support it.
"Country" "City" "street_name"
When selecting a country then the drop down list should be limited to the cities of that country and street_names to the streets of that city.
Many thanks,
Olivier
Hi Olivier,
Dynamic Data Validation isn't something built into Excel, and so getting it to work is done with a clever use of named ranges. As a result it's not scalable to the degree you're after.
You may be able to build a VBA data validation model but I don't have any tutorials I can point you to, sorry. You could try posting your question in the VBA forum.
Mynda
Hi Olivier
You could try pivoting your data and using slicers for Country, City etc as your dependent validation.
Apparently you can hide items with do data in the slicer in Excel 2013 but I am using Excel 2010.
Sunny
Hi Olivier,
I found a good solution using pivot tables that will allow you to create cascading data validation lists. No VBA required.
http://www.utteraccess.com/wiki/index.php/Using_Pivot_Tables_to_Manage_Cascading_Dropdown_Lists
Thanks for sharing that solution, Paula. It's very clever indeed.
Mynda
Olivier,
There are so many ways to do things in Excel. I have an other solution.
I just use one table with 2 columns. The first column shows the combination of all the choosen data validation-fields, the second column shows the new texts to show in the next data-validation-cell.
Maybe a little confusing (my english is not perfect) but see the attached file where you can find the data-validation in the three yellow cells and hopefully you'll understand the working of it.
Regards, Henk.
Thanks for sharing your solution, Henk. Another one I've not seen before.
My only concern is that the lists would be difficult to maintain. I suppose if the data was not likely to change then it would be ok as a one off job, but if it was a list of products or other items that were more fluid, then I'd prefer the PivotTable method as it's more dynamic.
Mynda
@Mynda, i agree, 'my datavalidation with 1 table is not the easiest one. And the PivotTable is more dynamic.
Henk.
