Indirect and Dynamic Range

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

Just use =Class_6 in the DVD

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"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

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

Just to clarify.......

As Bob noted, you can just use =Class_6 as the source if that's the only
range name you have. But, I have a feeling that you do have more (and didn't
tell us!) which is why I suggested the Choose formula. If you do have more
names than the Choose formula can be expanded to handle the other names (up
to 29).

Biff

"Biff" <biffinpitt@comcast.net> wrote in message
news:uAWWXNntGHA.324@TK2MSFTNGP06.phx.gbl...
> 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
>
>

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

Sorry I have confused it enough without complicating it even more. In
the last post i made an error as I meant that B11 was changing with
multiple lists , not E11 as I typed. The data validation list was
pointing to B11, ie =Indirect(B11) and a large number of named ranges
will be created and will appear as options in cell B11.

Graham

Graham Haughs wrote:
> 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
>>
>>
>>
>>

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

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

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

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.