Many thanks for this Harlan. I am just starting to get my head round it
but it is only 6 o'clock in the morning so it is a slow process! I am
grateful for all your efforts.

Graham

Harlan Grove wrote:
> Graham Haughs wrote...
>
>>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 Haughs" <gmh54@supanet.com> wrote in message
>>>>>
>>>>>>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.
>
>
> So Class_6 holds the result of an OFFSET call, and it happens to be
> based on column F in the Data worksheet. Column F is the 6th column of
> that worksheet. Is there a correspondence between columns in the
> worksheet and the number in the Class_# defined names? If so, you could
> make the validation range
>
> =INDEX(Data!$2:$2,B11):INDEX(Data!$A:$DP,COUNTA(INDEX(Data!$A:$DP,0,B11)),B11)
>
> Otherwise, you'd need a table of these defined names and their current
> addresses, use the B11 value as a lookup value into that table to pull
> the range address, then use that as the argument to INDIRECT. In that
> case, your data validation list reference would be something like
>
> =INDIRECT(VLOOKUP(B11,RefTbl,2,0))
>
> The first column of RefTbl would be your Class_# defined names, and the
> second column the corresponding addresses, which could be produced by a
> multistep process like
>
> 1. Enter text formula in topmost cell of 2nd column of RefTbl,
>
> ="=SUBSTITUTE(CELL(""Address"",(Data!A1,"&X101&")),""$A$1,"","""",1)"
>
> where X101 would be the topmost cell in the 1st column of RefTbl. Fill
> down so there's one of these formulas for each defined name.
>
> 2. Copy the column of formulas and paste special as values on top of
> itself.
>
> 3. With the range of former formulas still selected, run Edit > Replace
> and replace = with =. That might seem to be no change, but it has the
> effect of taking text constants that look like formulas and entering
> them as formulas. The result should be the range addresses of the
> defined names.
>