Linking 3 combo boxes in Excel

Microsoft Excel 2003 (full product)
February 13, 2010 at 04:07:36
Specs: Excel
Hello all

I have a question in excel
I have 3 different data lists:

list 1 is country name for example:
1- Italy
2- US
3- Japan
4- ...
list 2 contains the province or state name regarding the country name for example for US is:

1- California
2- Colombia
3- Texas
4-...

and the 3rd one is city name in each state/province for example regarding California:

1- Los angeles
2- San Francisco
3-...

I want to create a file which in column one (in every cell) user can select a "country" name by a drop-drawn list. then in column 2 in automaticaly show the related province/state in a drop-drwan list (i.e for US in Column1 only shows list of US States in column 2) and then in column 3 shows a list of cities accordingly (i.e for California just shows california cities)

I would be grateful if anyone can help me in this issue.

Thanks all,

Regards,
hsh


See More: Linking 3 combo boxes in Excel

Report •

#1
February 13, 2010 at 06:33:46
Your subject says ComboBoxes, yet you are asking about drop down lists in cells.

You also said "in column one (in every cell)" .

That's 65,536 cells. Is that what you really want?

Assuming you are asking about Dependent Drop Down Lists, here is the process:

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!


Report •

#2
February 13, 2010 at 06:39:15
It worked.
Thanks alot

Report •

#3
February 13, 2010 at 06:44:52
That was quick!

Glad to have been of assistance.


Report •

Related Solutions

#4
February 13, 2010 at 06:52:54
my first question was solved but another question has come up!
I have another list which that all 3 columns are numbers (some codes) means:
coulumn one are: 100,200,300
column two are: 110,120,...,190 (for 100 in column one) and
Dear Derby

the same for 200 and 300
column three are: 111,112,...,119 (for 100 in column one and 110 in column two) accordingly. and the same for others

the problem is that I can not define a number as a name so the drop-down list doesn't work here. do you have any solution for this?

Thanks in advance.


Report •

#5
February 13, 2010 at 07:41:00
re: "I can not define a number as a name"

You don't have to. That's the beauty of using INDIRECT. It will accept any text string and use it refer to a range.

Name the "number ranges" with names like my100, my200, etc.

Then use =INDIRECT("my" & A1)

With 100 in A1, this will evaluate to =INDIRECT("my100") and refer to that range.


Report •

#6
February 13, 2010 at 07:54:40
Excellent! that is great. many thanks
just one more question. of course it is not related to previous one!
How can I define a dynamic name in excel ( I think by using offset function). means for example if there is 2 numbers in my list my range only contains these 2 numbers in range name for example "my100" and if user adds a new number on the bottom of the list the range name "my100" extend to new number and will contain all 3 numbers.

Sorry I bother you a lot. thanks for answering.


Report •

#7
February 13, 2010 at 08:54:09
I need to ask you to post the dynamic range question as separate thread with a relevant subject line. It helps make it easier for members to search the archives for specific information.

Thanks.


Report •

#8
March 27, 2010 at 14:25:57
Hello,
I have similar problem, I have a list of country, a list of states/provinces and a list of representatives. The states/provinces and representative will change according to the country selected

list 1 is country name for example:
1- Italy
2- US
3- Canada
4- ...

list 2 contains the province or state name regarding the country name for example for US is:
1- California
2- Colombia
3- Texas
4-...

and the 3rd one is representative name in each country for example regarding US:
1- Michael
2- Cindy
3- Chan
4- ...

If select Canada in the first drop down, then the other two drop down will adjust accordingly. Any help would be greatly appreciated.
Thank you!


Report •

Ask Question