>I need 120 named ranges

I've never had to deal with that many in this context!

Let me see if I can come up with something. No guarantees!

Biff

"Graham Haughs" <gmh54@supanet.com> wrote in message
news:uKR68xntGHA.4852@TK2MSFTNGP02.phx.gbl...
> Thanks for your patience Biff, the problem is that I need 120 named
> ranges so I will have to re-think strategy as you have confirmed that
> indirect is no use in ths situation.
>
> Graham
>
> Biff wrote:
>> Exactly how many names do you have? Choose will work with up to 29 names.
>>
>>
>>>I really need the indirect function
>>
>>
>> Forget about Indirect. It won't work! Indirect needs a TEXT
>> representation of a reference. When you use =INDIRECT(B11) the name in
>> B11 evaluates to the FORMULA: =OFFSET(.....................). This is not
>> a TEXT representation of a reference!
>>
>> Try this:
>>
>> List the names in a range of cells, say, AA1:AAn
>>
>> AA1 = Class_1
>> AA2 = Class_2
>> AA3 = Class_3
>> etc
>>
>> As the source for the drop down:
>>
>> =CHOOSE(MATCH(B11,AA1:AA3,0),Class_1,Class_2,Class_3)
>>
>> Biff
>>
>> "Graham Haughs" <gmh54@supanet.com> wrote in message
>> news:%23OWSBWntGHA.3568@TK2MSFTNGP05.phx.gbl...
>>
>>>Thanks for feedback but I really need the indirect function. I know I can
>>>use =Class_6 etc but E11 will be changing, as it is a drop down list. It
>>>may be Class_5, Class_6, Class_12 etc so the data validation has to pick
>>>up the named range which this indicates. If it was not a dynamic range it
>>>works fine, I just do =E11 and whatever value appears in cell E11 the
>>>appropriate named range will appear as the drop down list, but NOT when
>>>it is a dynamic range.
>>>Sorry to be a pain but I really need it to do this.
>>>
>>>Graham
>>>
>>>Biff wrote:
>>>
>>>>Hi!
>>>>
>>>>Do you enter more than just "Class_6" into B11?
>>>>
>>>>Try this as the source for the drop down:
>>>>
>>>>=CHOOSE(1,Class_6)
>>>>
>>>>Biff
>>>>
>>>>"Graham Haughs" <gmh54@supanet.com> wrote in message
>>>>news:O87WysmtGHA.2172@TK2MSFTNGP05.phx.gbl...
>>>>
>>>>
>>>>>I have a named dynamic range Class_6 which holds the dynamic range
>>>>>=OFFSET(Data!$F$2,0,0,COUNTA(Data!$F:$F),1)
>>>>>I create a data validation list and put the location as =INDIRECT(B11)
>>>>>In cell B11 is the value Class_6
>>>>>When I try to enter =indirect(B11) in the data validation I get the
>>>>>message "The source currently evaluates to an error"
>>>>>I can get the indirect to work with a named list but not with a dynamic
>>>>>range list. Am I doing something wrong or will it not do this. I value
>>>>>any help.
>>>>>
>>>>>Kind regards,
>>>>>Graham Haughs
>>>>>Turriff
>>>>>Scotland
>>>>
>>>>
>>