Dropdown list values based on another cells value?

[creative999]

Is it possible to have a dropdown list show different values based on a value entered in another cell? For example:

If A1 = OPEN, then data validation dropdown list source in B2 = Y1:Y10
If A1 = CLOSED, then data validation dropdown list in source B2 = Z1:Z10

 

[etaf]

i have used a formula in LIST - although it gives an error - I went to custom and then back to list and it works

=IF(A1="OPEN",Y1:Y10,IF(A1="Closed",Z1:Z10,""))

Book9
	A	B	C																			V	W	X	Y	Z	AA
1	closed																								open	closed
2																									1	a
3																									2	b
4																									3	c
5																									4	d
6																									5	e
7
8
9
10
11
12
13	If A1 = OPEN, then data validation dropdown list source in B2 = Y1:Y10
14	If A1 = CLOSED, then data validation dropdown list in source B2 = Z1:Z10
15
Sheet4

Cells with Data Validation
Cell	Allow	Criteria
B1	List	=IF(A1="OPEN",Y1:Y10,IF(A1="Closed",Z1:Z10,""))

i have also provided a dropbox link - BUT will only be available for a few days

data list - different ranges -ETAF.xlsx

Shared with Dropbox

www.dropbox.com

 

[creative999]

THANK YOU.... Worked perfectly.

 

[etaf]

you are welcome