|Hello I am trying to following these instructions below in Excel 2010 but when I tried to use the same name twice I receive a message to use a unique name. So the solution doesn't work in Excel 2010. Do you have a solution?|
The Data Lists and Named Ranges
1 - Create the list of Countries in an out-of-the-way or hidden column.
2 - Create a individual list of provinces/states for each country in the same manner.
3 - After each province/state list is created, select the list and create a Named Range, giving that list the exact name of corresponding country you used in the Country list.
4 - Create the list of cities for each province/state.
5 - After each city list is created, select the list and create a Named Range, giving that list the exact name of the corresponding province/state you used in the province/state list.
The Drop Downs
1 - Assuming the Country Drop Downs will go in Column A, select as many cells in Column A that you want to contain Drop Downs.
2 - Go to Data...Validation...Settings tab...Allow field and choose List.
3 - In the Source field enter the range that contains the countries. This will create a Country List drop down in each selected cell.
4 - In the column where you want the province/state drop downs, select the cells and go to Data...Validation...Setting tab...Allow field and choose List.
5 - In the Source field enter =INDIRECT(A1), assuming A1 contains your first country drop down. When you do this, Data Validation will use the Named Range that matches the country whose name is in A1 and populate the list with those provinces/states.
6 - Repeat this process for the city drop downs using =INDIRECT(B1), or whatever cell it is that contains the first province/state drop down. Once again, the named ranges will be picked up and the drop downs populated accordingly.
Hope that helps!