Drop Down List Question

[nowhereman]

Does anyone know how you can make a cell show a different drop down list
to pick form depending on the contents of another cell.

example

i now have data valadation for lets say cell C1. The valadation is a
list that is a named range, with the name being "List1" I know how to
use data valadation select cell C1 and then choose list and =List1 to
accomplish this example. so that when i select cell c1 i get a drop down
list for the all the data in the cells which are in the range named
"list1"

Now suppose i want the list that drops down for cell C1 dependant on the
value in Cell B1. for example if B1=1 then the drop down in C1 would be
"List1", and if Cell B1=2 the drop down in C1 would be "List2" and if B1=
3;"List3, and so forth.

I must admit i cannot figure out how to make this work. I am hoping
someone is able to know the answer to my problem here.


If it is only possbiile with VBA programing i would appreciate if you
could help me with the code and how to activate it, as i know next to
nothing about vba.

Many thnaks in advance for any help you have to offer

NWM

 

[Ashish Mathur]

Hi,

Please refer to this

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[T. Valko]

As the source for the drop down in cell C1 use this formula:

=CHOOSE(B1,List1,List2,List3)

Depending on what version of Excel you're using you can have as many as 29
or 254 named lists at your disposal.

 

[T. Valko]

P.S.

If cell B1 is empty while you're setting up the validation you will get a
message saying something like:

The source currently evaluates to an error.....

Just answer YES.

 

[Shane Devenshire]

Hi,

Choose Data, Validation, List and enter

=INDIRECT("List"&B1)

 

[T. Valko]

=INDIRECT("List"&B1)

If Listx is a dynamic range defined using functions like OFFSET that won't
work.