Drop Down List Question

  • Thread starter Thread starter nowhereman
  • Start date Start date
N

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
 
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.
 
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.
 
=INDIRECT("List"&B1)

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