drop list dependant on other drop list selection

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

intjdragonuser - 14 Apr 2004 14:07 GMT

How do I make a drop list that is used with the cell-validation feature
depend on the selection from the drop list/cell validation of a
previously filled cell?

Example: Cell A2 can be Hot, Warm, or Cold. If Hot is selected, cell
A3 can be red, yellow or orange. If Warm is selected, cell A3 can be
black, gray or white. If Cold is selected, cell A3 can be blue, green
or purple.

Individual lists may be up to 25 items long.

Reply to this Message

Frank Kabel - 14 Apr 2004 14:12 GMT

Hi
for an example see:
http://www.contextures.com/xlDataVal02.html

Signature

--
Regards
Frank Kabel
Frankfurt, Germany

> How do I make a drop list that is used with the cell-validation
> feature depend on the selection from the drop list/cell validation of
[quoted text clipped - 10 lines]
> Excel Templates, Training, Add-ins & Business Software Galore!
> Free Excel Forum http://www.ozgrid.com/forum ***

Reply to this Message

Andy B - 14 Apr 2004 14:13 GMT

Hi

Have a look here:
http://www.contextures.com/xlDataVal02.html

Signature

Andy.

> How do I make a drop list that is used with the cell-validation feature
> depend on the selection from the drop list/cell validation of a
[quoted text clipped - 10 lines]
> Excel Templates, Training, Add-ins & Business Software Galore!
> Free Excel Forum http://www.ozgrid.com/forum ***

Reply to this Message

intjdragonuser - 20 Apr 2004 19:27 GMT

The Contextures produce sample with the INDIRECT function requires the
dependant cells to run in parallel columns.

My cell B2 will be filled from a "Town" list, then cells from A6 on down
column A should have droplists (Streets) dependant on the answer in B2.
Rows 1 - 4 have static data except for B2. Col C will fill
automatically depending on the item selected for cells in col A.

Someone sent a sample sheet with "Supervisor" and Staff (I'm at another
office) Everything works, but I can't break the code on the syntax!

The second cell (my A6) has the validation Fx Source:
=INDIRECT(VLOOKUP(Supervisor,SupervisorLookup,2,False)).
"Supervisor" is a Defined Name =Sheet1!$A$1. "SupervisorLookup"
=Sheet1!$D$2:$E$5. I do not know why the ranges were named, what "2"
signifies, and why the "FALSE".

I have 2 days to finish this thing...

Reply to this Message

Debra Dalgleish - 21 Apr 2004 03:14 GMT

You can use the Dependent Lists the you saw on my web site:

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

However, instead of using a relative reference, e.g. =INDIRECT(A2),
use an absolute reference to cell B2: =INDIRECT($B$2)

> The Contextures produce sample with the INDIRECT function requires the
> dependant cells to run in parallel columns.
[quoted text clipped - 18 lines]
> Excel Templates, Training, Add-ins & Business Software Galore!
> Free Excel Forum http://www.ozgrid.com/forum ***

Signature

Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Reply to this Message

intjdragonuser - 21 Apr 2004 19:03 GMT

Debra,

=INDIRECT($B$2) worked!

Easy when you know how. Thank you.

Carol

Reply to this Message

Dave Hawley - 21 Apr 2004 02:47 GMT

Hi,

If you go here
http://www.ozgrid.com/download/default.htm

and download "MatchingLists.zip" it should help.

Reply to this Message