Drop Down List

is it possible to have different dropdown lists appear
depending on value in cell to the left of where you have
your dropdown list. e.g

if A1 = "LP" then b1="Dropdownlist1"
if A1 = MP then b1="Dropdownlist2"

etc etc

Hope you can help

Pete

There are instructions for dependent data validation lists here:

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


Pete wrote:
> is it possible to have different dropdown lists appear
> depending on value in cell to the left of where you have
> your dropdown list. e.g
>
> if A1 = "LP" then b1="Dropdownlist1"
> if A1 = MP then b1="Dropdownlist2"
>
> etc etc
>
> Hope you can help
>
> Pete


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

Hi

Into data validation list's source enter the formula
=IF($A1="LP",Dropdownlist1,IF($A1="MP",Dropdownlist2,""))
where Dropdownlist1 and Dropdownlist2 are named ranges.

Or, when you have to cope with many sublists, then maybe you arrange them as
matrix on separate sheet (p.e. Lists), like
LP MP ....
lp1 mp1 ....
lp2 mp2 ....
.... ... ...

Now define the named range for data validation lists source as dynamic named
range, like (on fly, so you have to test it yourself, but I hope you get the
idea)
YourList=OFFSET(Lists!$A$2,0,MATCH($A1,Lists!$A$1:$Z$1,0)-1,COUNTIF(OFFSET(L
ists!$A2,0,MATCH($A1,Lists!$A$1:$Z$1,0)-1,100,1),"<>")-1,1)

Or, when all sublists are always of same length, you can use INDEX function
to get a single column from matrix (instead of OFFSET)


Arvi Laanemets


"Pete" <[email protected]> wrote in message
news:[email protected]...
> is it possible to have different dropdown lists appear
> depending on value in cell to the left of where you have
> your dropdown list. e.g
>
> if A1 = "LP" then b1="Dropdownlist1"
> if A1 = MP then b1="Dropdownlist2"
>
> etc etc
>
> Hope you can help
>
> Pete

Brilliant Debra, Exactly what I want thanks.

>-----Original Message-----
>There are instructions for dependent data validation
lists here:
>
> http://www.contextures.com/xlDataVal02.html
>
>
>Pete wrote:
>> is it possible to have different dropdown lists appear
>> depending on value in cell to the left of where you
have
>> your dropdown list. e.g
>>
>> if A1 = "LP" then b1="Dropdownlist1"
>> if A1 = MP then b1="Dropdownlist2"
>>
>> etc etc
>>
>> Hope you can help
>>
>> Pete
>
>
>--
>Debra Dalgleish
>Excel FAQ, Tips & Book List
>http://www.contextures.com/tiptech.html
>
>.
>