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
"Tom Ogilvy" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> =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" <(E-Mail Removed)> wrote in message
> news:sICdnYSmUMQLve7YnZ2dnUVZ_u-(E-Mail Removed)...
>> Hi Tom,
>>
>> Could you please elaborate a bit on your formula? I'm not getting the
>> jest of it.
>>
>> Thanks.
>> Regards,
>> Howard
>>
>> "Tom Ogilvy" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> =if(D1<=27,Choose(D1, . . .),Choose(D1-27,...))
>>>
>>> --
>>> Regards,
>>> Tom Ogilvy
>>>
>>> "dkingston" <(E-Mail Removed)> wrote in message
>>> news:6B8B3B36-D124-46FA-8A4A-(E-Mail Removed)...
>>>> 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" <(E-Mail Removed)> wrote in message
>>>>> news:539DF5F5-B532-41CE-99D8-(E-Mail Removed)...
>>>>> >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?
>>>>>
>>>>>
>>>>>
>>>
>>>
>>
>>
>
>
Similar Threads
