Dropdown Menu?

[zoran144]

Hi,

This is driving me a little insane.... so please help.

Ok here's the setup: I have 3 columns - 1st being the states, 2nd column
shows cities in those states, and 3rd has climate zone values.

Some states have 3 cities some 55...

Alltogether i have over 700 cities...


1. I want to select a state from a drop down menu (this i know how to do)
2. Next I want to select a city but I want to have a drop down menu that has
only cities within the state selected;
3. and last i want to display the climate zone value for that selected
city....

Thanks a million!

 

[Don Guillett]

http://www.contextures.com/tiptech.html

 

[Max]

Debra Dalgleish has material on this at her:
http://www.contextures.com/xlDataVal02.html
Excel -- Data Validation -- Create Dependent Lists
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik

 

[Gord Dibben]

You need dependent lists dropdowns.

See Debra's site for this.

http://www.contextures.com/xlDataVal02.html


Gord Dibben MS Excel MVP

 

[zoran144]

Thanks guys!!!!! Got the answer here:
http://www.contextures.com/xlDataVal13.html

Great website - you wont see any questions from me in a while now.

 

[Cheers,]

Hi,

Here are the steps for creating a dynamic data validation list:

1. Create three or more list in the spreadsheet. For example:
A B C
New York Quebec Canada
Seattle Toronto US
Miami Victoria

You would name lists: US (A1:A3), Canada (B1:B3), Countries (C1:C2) this
last is optional

2. Select the cell where you want the initial validation list, for this
example, D1
3. Choose Data, Validation
4. From the Allow drop-down and choose List
5. In the Source box enter the following formula
=Countries
6. Move to the location where you want the dynamic (changing) list to
appear, say for example E1
7. Choose Data, Validation, List and in the Source box enter the formula
=INDIRECT(D1)

You can also make a self-referential dynamic data validation list.

Then to display the data for that city/state:

=SUMPRODUCT(--(A1:A600=City),--(B1:B600=State),Climate)

Where City is the cell where the city drop down is located and State is the
cell where the State drop-down is located, and Climate is the column where
the climate data is displayed.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire