Indirect and Dynamic Range

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.

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