=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" wrote in message
...
Hi Tom,

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

Thanks.
Regards,
Howard

"Tom Ogilvy" wrote in message
...
=if(D1<=27,Choose(D1, . . .),Choose(D1-27,...))

--
Regards,
Tom Ogilvy

"dkingston" wrote in message
...
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.

"L. Howard Kittle" wrote:

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" wrote in message
...
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?