Conditional Drop Down Lists. (Excel 97)

Topic

New Reply

Hi folks,

I’m needing to find a ‘simple’ solution (i.e. would prefer not to get into VB, if I can help it) that would allow me to build into a spreadsheet a series of dropdown lists (possibly multi-columned) that when a selection is made in the first, the contents of the second drop down list changes depending on the value selected in the first, and so on. All values selected would be compiled on a seperate sheet for printing. I have looked at VLOOKUP and CHOOSE but I am now assuming the ‘forms’ controls will need to be used, but I can’t see anything obvious to allow me to do this.

Any ideas or similar example/sample sheets would be greatly appreciated.

AP

Reply | Quote

Replies

Hi, AP. I don’t know if you are familiar with Data Validation, but what you ask can be done without VBA macros. See the example below where I want a person to pick a state in cell G2 and choose the city in cell I2. I want the cities list to change according to the state chosen in G2…

1) Name some ranges with list content you desire.
Example:
A3:A4 is named “STATES”
TX
AL

Cells B3:B6 is named “TX”
Houston
Dallas
San Antonio
Lubbock

Cells C3:C6 is named “AL”
Mobile
Huntsville
Dothan
Montgomery

Cell D3 is named “NoState”
Pick a state in G2

For the first drop down list (Data Validation) choose for the list source, =STATES

For the second drop down list (Data Validation) choose for the list source, =IF(G2=”TX”,TX,IF(G2=”AL”,AL,NoState))

I’m not sure what you meant by the second part of the question, but this should give you some ideas.

-Lenny

Reply | Quote

For the second drop down list, rather than nested IF’s, you could use the INDIRECT function. In your example, the source for the list would be =INDIRECT($G$2).

Reply | Quote

Good point.

-Lenny

Reply | Quote

I agree.

However, if you have no more than 3 lists to activate at every choice point, I’d go for your IF-formulation.

The reason: INDIRECT is a volatile function that prolongs the recalc time. If one can avoid using it, one can better do so (I will).

Aladin

Reply | Quote

I set it up both ways (nestred IF and INDIRECT). Using the Nested IF, the user can erase the contents of G2 (State) and the drop down list for City (I2) lets user select a blank entry from the drop down list. If you set it up with INDIRECT and erase the contents of G2 (State), the user cannot access the drop down list for City in I2. A small point but may be important to designer.
Chuck

Reply | Quote

Debra Dalgleish has a step by step example posted at:

http://www.contextures.on.ca/xlDataVal02.html%5B/url%5D

It is very nicely done with graphics.

Jim Cone
San Jose, CA

Hyperlinks added- Mod

Reply | Quote

Thanks for all your responses. They provided me with exactly what I was looking for.

Thanks again.
AP

Reply | Quote

Jeez, that would have saved me a lot of typing.

-Lenny

Reply | Quote