3 Related Dropdown from 2 tables in Excel

April 2, 2010 at 21:33:53
Specs: Windows XP
Hello,

I would to find out if the following scenario if it is possible. I have 2 tables (see below)

Table#1 States/Prov. for each country

Country
USA
Canada
Japan


USA
California
Arizona
Nevada

CANADA
Quebec
Ontario
Alberta


JAPAN
Tosando
Tokaido
Hokurikudo

Table #2 Representatives for each country

Country
USA
Canada
Japan


USA
Mike
David
Cindy

CANADA
Wendy
Henry
Debbie

JAPAN
Haruka
Kimi
Ashan

I am trying to create 3 dropdowns, 1st for country, 2nd for States/Prov. and 3rd for Representative

If your select USA as country in the 1st drop down, the 2nd drop for States/Prov. should have California, Arizona and Nevada as option and the 3rd drop down for Representative should have Mike, David and Cindy as option.

If you select Canada as country in the 1st drop down, the 2nd drop down for States/Prov. should have Quebec, Ontario and Alberta as option and the 3rd drop down for Representative should have Wendy, Henry and Debbie as option.

Finally, if you select Japan as country in the 1st drop down, the 2nd drop down for States/Prov. should have Tokaido, Tosando and HokuriKudo as option and the 3rd drop down for Representative should have Haruka, Kimi and Ashan as option.

Basically, 2nd and 3rd drop down is depending the selection of the 1st dropdown. I was able to linked drop for country with drop down for States/Prov. (using "INDIRECT" function), but unable to link drop down #3 with #1. Is this possible?

Regards,
Van


See More: 3 Related Dropdown from 2 tables in Excel

Report •


#1
April 3, 2010 at 06:04:19
Hi,

If your second drop down for the regions uses named ranges:
USA
Canada
Japan
then create three new named ranges for the representatives for each country. The named ranges are "R" plus the country name:
RUSA
RCanada
RJapan

In the drop down for the Reps select Data Validation... List and in 'Source:' enter this formula:

=INDIRECT("R" & A2)

The Reps drop down list will now change depending on the country selected in the first drop down. In my example the Country drop-down was in cell A2, change the formula as appropriate, to point to the Country selected.

Regards


Report •

#2
April 3, 2010 at 22:26:30
It works, THANK YOU SOOO MUCH!!!!

Van


Report •

#3
April 4, 2010 at 04:45:22
You're welcome,

and thanks for the feedback.

Regards

Humar


Report •

Related Solutions


Ask Question