Solved Data lists and naming ranges.

March 20, 2013 at 12:30:10
Specs: Windows XP
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!


See More: Data lists and naming ranges.

Report •


#1
March 20, 2013 at 13:52:34
✔ Best Answer
re: "... but when I tried to use the same name twice I receive a message to use a unique name."

You can't use the same name for more than one range in the same workbook. How would Excel know what range the name referred to when used in a formula?

e.g. If "MyName" referred to A1:A10 and B1:B10, how would Excel evaluate something like:

=SUM(MyRange)

Why are you trying to use the same name twice? I don't see anything in those instructions that says to do that.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 21, 2013 at 09:34:32
Got it. Thank you so much!

Report •

#3
March 21, 2013 at 10:18:13
I am trying to have the drop downs link to a pivot table page filter. I am able to do that with a combobox form control and a macro. I don't see where I am able to assign a macro to the drop down box like I can with a combo box. Any suggestions?

Report •

Related Solutions

#4
March 21, 2013 at 15:19:52
This appears to be a different question than the one that started this thread. The instructions in your OP are for creating Dependent Drop Down lists.

Please start a new thread with a relevant Subject Line.

DerbyDad03
Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question