data validation from multiple lists

[dkingston]

i have data validation for a cell set to allow data from a list using a named
range. can i make the name of the range in the data validation source field
dependent on the value in another cell?
ie: if i have 3 named ranges in rows 1:3 in columns a, b & c respectively
and want the drop down in E1 to display only 1 of the 3 lists depending on
the value in cell D2 how do i set up the data validation field?

 

[L. Howard Kittle]

You posted to Programming, so you may want a VBA solution, however, this
should work for you for a worksheet solution.

A1:A3 is named AAA
B1:B3 is named BBB
C1:C3 is named CCC

A5:A7 = AAA, BBB, CCC

D1 > Data Valadation > Allow > List > Source > =A5:A7 > OK

E1 > Data Valadation > Allow > List > Source >
=CHOOSE(MATCH($D$1,$A$5:$A$7,0),AAA,BBB,CCC) > OK

If you have trouble getting it together, I can send you an example workbook.

HTH
Regards,
Howard

 

[dkingston]

The worksheet solution works perfectly. Thank you.
I still have 1 problem though. Excel help says the CHOOSE function will only
allow 29 values. I actually have 54 lists to choose from. Is there a way
around this? I tried breaking my lists into 2 groups but Excel doesn't allow
an IF function in the source field in data validation.

 

[Tom Ogilvy]

=if(D1<=27,Choose(D1, . . .),Choose(D1-27,...))

 

[L. Howard Kittle]

I have run into the same 29 limit problem. Could not find a way around it.

I see Tom has offered a solution, however, I am having trouble deciphering
it.

Regards,
Howard

 

[L. Howard Kittle]

Hi Tom,

Could you please elaborate a bit on your formula? I'm not getting the jest
of it.

Thanks.
Regards,
Howard

 

[Tom Ogilvy]

=IF(AND(D1>=1,D1<=6),IF(D1<=3,CHOOSE(D1,"A","B","C"),CHOOSE(D1-3,"D","E","F")),"")

in D1 successively enter the numbers 1 to 6 inclusive.

 

[L. Howard Kittle]

Thanks, Tom, I'll give it a go.

Regards,
Howard

=IF(AND(D1>=1,D1<=6),IF(D1<=3,CHOOSE(D1,"A","B","C"),CHOOSE(D1-3,"D","E","F")),"")

in D1 successively enter the numbers 1 to 6 inclusive.

 

[L. Howard Kittle]

Hi Tom,

Okay, got it, and thanks. I expanded to 29 choices for each CHOOSE, a1 to
a29 and b1 to b29. Was having trouble with the second CHOOSE with the
formula as below. Would only return value if false.

IF(AND(D1>=1,D1<=29),IF(D1<=29,CHOOSE(D1,"a1",... to
....,"a29"),CHOOSE(D1-29,"b1",... to ...,"b29")),"")

Finally dawned on me that first <=29 needed to be <=58... DUH!

Thanks for the help, always good stuff from you and this group.

Regards,
Howard