Conditional Cell-Validation

[Erik_AA__1]

Using Excel 2002, I've been trying to create a drop down menu that displays several options from a list, which should be simple, however, I ran into a problem when I decided that I needed the oprions in one drop down menu to be conditional based on the option selected from another drop menu.

Example:

A3 - drops down to show 3 options

Red
Blue
Green

If Red is selected, then A4 will display drop down options:

1
2
3

If Blue is selected, A4 should show:

4
5
6

If Green is selected, A4 should allow the user to select:

7
8
9

This posed a problem when I found out that Validation won't allow me to input a formula. Does anybody know if what I'm trying to do here is even possible with Excel 2002?

[snasui]

Try this,

1. F1:F3
Red
Blue
Green

2. G1:G4
Red
1
2
3

3. H1:H4
Blue
4
5
6

4. I1:I4
Green
7
8
9

5. Select A3 > Data > Validation > Allow:List > Source :=OFFSET($F$1,0,0,COUNTA($F:$F),1)

6. Select A4 > Data > Validation > Allow: List > Source: =OFFSET($G$1,1,MATCH($A$3,$G$1:$I$1,0)-1,COUNTA(INDEX($G:$I,0,MATCH($A$3,$G$1:$I$1,0)))-1,1)

Adjust range for your suit.

Hope this helps.
------
snasui

[darkyam]

If you have 3 blank cells you can play with, in the first, put =IF($A$3="Red",1,IF($A$3="Blue",4,7)) and do the same in the next two cells with 2, 5, and 8 and 3, 6, and 9. Then in the validation formula, just refer to these three cells.

[EdMac]

Check out this site which has all the information you could want on dependant drop down

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

Ed