Re: data validation from multiple lists

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

=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.

--
Regards,
Tom Ogilvy


"L. Howard Kittle" <lhkittle@xxxxxxxxxxx> wrote in message
news:sICdnYSmUMQLve7YnZ2dnUVZ_u-dnZ2d@xxxxxxxxxxxxxx
Hi Tom,

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

Thanks.
Regards,
Howard

"Tom Ogilvy" <twogilvy@xxxxxxx> wrote in message
news:OWLUKgwFHHA.536@xxxxxxxxxxxxxxxxxxxxxxx
=if(D1<=27,Choose(D1, . . .),Choose(D1-27,...))

--
Regards,
Tom Ogilvy

"dkingston" <dkingston@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6B8B3B36-D124-46FA-8A4A-5FCC5B53BCA5@xxxxxxxxxxxxxxxx
The work*** 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.

"L. Howard Kittle" wrote:

You posted to Programming, so you may want a VBA solution, however,
this
should work for you for a work*** 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" <dkingston@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:539DF5F5-B532-41CE-99D8-1812957F2534@xxxxxxxxxxxxxxxx
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?









.

Quantcast