How do you Identify text as a named range in excel

Is there a way to identify to excel in that the following formula:
=SUM(INDIRECT(C1&"2005.355")),
this part INDIRECT(C1&"2005.355") is a named range in the wb?


Where c1=Jan
and,
the named range is Jan2005.355.

This refers to the same question I posted a few minutes ago if it will
help understand the question more.

Thanks
Dave

Dave,

I've responded to your other post.

Pete

Hi!

Is the named range just a named range:

Jan2005.355 refers to:

=Sheet1!$A$1:$A$10

Or, is the named range really a named formula (dynamic range):

Jan2005.355 refers to:

=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A))

If it's the latter, Indirect won't work.

Biff

"DMDave" <[email protected]> wrote in message
news:[email protected]...
> Is there a way to identify to excel in that the following formula:
> =SUM(INDIRECT(C1&"2005.355")),
> this part INDIRECT(C1&"2005.355") is a named range in the wb?
>
>
> Where c1=Jan
> and,
> the named range is Jan2005.355.
>
> This refers to the same question I posted a few minutes ago if it will
> help understand the question more.
>
> Thanks
> Dave

Hi Biff,
The named range is just a named range
=NS2005!$C$9,NS2005!$C$17,NS2005!$C$25,NS2005!$C$33,NS2005!$C$41

For some reason Excel isn't recognizing the result of the indirect as
a named range. No matter what I do it just returns a #REF.
Im stumped.
If I just use =SUM(Jan2005.355) it does return the correct answer but
I have to put in a variable to be able to change the month from Jan to
Feb to Mar etc...
Is there a possible alternative solution?

Thanks
Dave

On Sat, 6 May 2006 21:50:50 -0400, "Biff" <[email protected]>
wrote:

>Hi!
>
>Is the named range just a named range:
>
>Jan2005.355 refers to:
>
>=Sheet1!$A$1:$A$10
>
>Or, is the named range really a named formula (dynamic range):
>
>Jan2005.355 refers to:
>
> =OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A))
>
>If it's the latter, Indirect won't work.
>
>Biff
>
>"DMDave" <[email protected]> wrote in message
>news:[email protected]...
>> Is there a way to identify to excel in that the following formula:
>> =SUM(INDIRECT(C1&"2005.355")),
>> this part INDIRECT(C1&"2005.355") is a named range in the wb?
>>
>>
>> Where c1=Jan
>> and,
>> the named range is Jan2005.355.
>>
>> This refers to the same question I posted a few minutes ago if it will
>> help understand the question more.
>>
>> Thanks
>> Dave
>

>The named range is just a named range
>=NS2005!$C$9,NS2005!$C$17,NS2005!$C$25,NS2005!$C$33,NS2005!$C$41

That still won't work with Indirect because it's not a contiguous single
range reference . To Indirect, it's 5 separate arguments.

About the only thing I can think of is something like this:

=SUM(CHOOSE(MATCH(C1,{"Jan","Feb"},0),Jan2005.355,Feb2005.355))

This could be quite long if you have 12 named ranges, 1 for each month.

You can make it a little shorter by creating a named formula for the months:

Months
Refers to: ={"Jan","Feb","Mar",etc., etc., "Dec"}

Or, list the months in a range of cells:

A1 = Jan
A2 = Feb
A3 = Mar
etc
A12 = Dec

Then:

=SUM(CHOOSE(MATCH(C1,Months,0),Jan2005.355,Feb2005.355,etc.,etc.,Dec2005.355))

Or:

=SUM(CHOOSE(MATCH(C1,A1:A12,0),Jan2005.355,Feb2005.355,etc.,etc.,Dec2005.355))

Biff

"DMDave" <[email protected]> wrote in message
news:[email protected]...
> Hi Biff,
> The named range is just a named range
> =NS2005!$C$9,NS2005!$C$17,NS2005!$C$25,NS2005!$C$33,NS2005!$C$41
>
> For some reason Excel isn't recognizing the result of the indirect as
> a named range. No matter what I do it just returns a #REF.
> Im stumped.
> If I just use =SUM(Jan2005.355) it does return the correct answer but
> I have to put in a variable to be able to change the month from Jan to
> Feb to Mar etc...
> Is there a possible alternative solution?
>
> Thanks
> Dave
>
> On Sat, 6 May 2006 21:50:50 -0400, "Biff" <[email protected]>
> wrote:
>
>>Hi!
>>
>>Is the named range just a named range:
>>
>>Jan2005.355 refers to:
>>
>>=Sheet1!$A$1:$A$10
>>
>>Or, is the named range really a named formula (dynamic range):
>>
>>Jan2005.355 refers to:
>>
>> =OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A))
>>
>>If it's the latter, Indirect won't work.
>>
>>Biff
>>
>>"DMDave" <[email protected]> wrote in message
>>news:[email protected]...
>>> Is there a way to identify to excel in that the following formula:
>>> =SUM(INDIRECT(C1&"2005.355")),
>>> this part INDIRECT(C1&"2005.355") is a named range in the wb?
>>>
>>>
>>> Where c1=Jan
>>> and,
>>> the named range is Jan2005.355.
>>>
>>> This refers to the same question I posted a few minutes ago if it will
>>> help understand the question more.
>>>
>>> Thanks
>>> Dave
>>
>

Biff, Thanks so much. That worked great!



On Sun, 7 May 2006 00:01:36 -0400, "Biff" <[email protected]>
wrote:

>>The named range is just a named range
>>=NS2005!$C$9,NS2005!$C$17,NS2005!$C$25,NS2005!$C$33,NS2005!$C$41
>
>That still won't work with Indirect because it's not a contiguous single
>range reference . To Indirect, it's 5 separate arguments.
>
>About the only thing I can think of is something like this:
>
>=SUM(CHOOSE(MATCH(C1,{"Jan","Feb"},0),Jan2005.355,Feb2005.355))
>
>This could be quite long if you have 12 named ranges, 1 for each month.
>
>You can make it a little shorter by creating a named formula for the months:
>
>Months
>Refers to: ={"Jan","Feb","Mar",etc., etc., "Dec"}
>
>Or, list the months in a range of cells:
>
>A1 = Jan
>A2 = Feb
>A3 = Mar
>etc
>A12 = Dec
>
>Then:
>
>=SUM(CHOOSE(MATCH(C1,Months,0),Jan2005.355,Feb2005.355,etc.,etc.,Dec2005.355))
>
>Or:
>
>=SUM(CHOOSE(MATCH(C1,A1:A12,0),Jan2005.355,Feb2005.355,etc.,etc.,Dec2005.355))
>
>Biff
>
>"DMDave" <[email protected]> wrote in message
>news:[email protected]...
>> Hi Biff,
>> The named range is just a named range
>> =NS2005!$C$9,NS2005!$C$17,NS2005!$C$25,NS2005!$C$33,NS2005!$C$41
>>
>> For some reason Excel isn't recognizing the result of the indirect as
>> a named range. No matter what I do it just returns a #REF.
>> Im stumped.
>> If I just use =SUM(Jan2005.355) it does return the correct answer but
>> I have to put in a variable to be able to change the month from Jan to
>> Feb to Mar etc...
>> Is there a possible alternative solution?
>>
>> Thanks
>> Dave
>>
>> On Sat, 6 May 2006 21:50:50 -0400, "Biff" <[email protected]>
>> wrote:
>>
>>>Hi!
>>>
>>>Is the named range just a named range:
>>>
>>>Jan2005.355 refers to:
>>>
>>>=Sheet1!$A$1:$A$10
>>>
>>>Or, is the named range really a named formula (dynamic range):
>>>
>>>Jan2005.355 refers to:
>>>
>>> =OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A))
>>>
>>>If it's the latter, Indirect won't work.
>>>
>>>Biff
>>>
>>>"DMDave" <[email protected]> wrote in message
>>>news:[email protected]...
>>>> Is there a way to identify to excel in that the following formula:
>>>> =SUM(INDIRECT(C1&"2005.355")),
>>>> this part INDIRECT(C1&"2005.355") is a named range in the wb?
>>>>
>>>>
>>>> Where c1=Jan
>>>> and,
>>>> the named range is Jan2005.355.
>>>>
>>>> This refers to the same question I posted a few minutes ago if it will
>>>> help understand the question more.
>>>>
>>>> Thanks
>>>> Dave
>>>
>>
>

Biff, Thanks so much. That worked great!



On Sun, 7 May 2006 00:01:36 -0400, "Biff" <[email protected]>
wrote:

>>The named range is just a named range
>>=NS2005!$C$9,NS2005!$C$17,NS2005!$C$25,NS2005!$C$33,NS2005!$C$41
>
>That still won't work with Indirect because it's not a contiguous single
>range reference . To Indirect, it's 5 separate arguments.
>
>About the only thing I can think of is something like this:
>
>=SUM(CHOOSE(MATCH(C1,{"Jan","Feb"},0),Jan2005.355,Feb2005.355))
>
>This could be quite long if you have 12 named ranges, 1 for each month.
>
>You can make it a little shorter by creating a named formula for the months:
>
>Months
>Refers to: ={"Jan","Feb","Mar",etc., etc., "Dec"}
>
>Or, list the months in a range of cells:
>
>A1 = Jan
>A2 = Feb
>A3 = Mar
>etc
>A12 = Dec
>
>Then:
>
>=SUM(CHOOSE(MATCH(C1,Months,0),Jan2005.355,Feb2005.355,etc.,etc.,Dec2005.355))
>
>Or:
>
>=SUM(CHOOSE(MATCH(C1,A1:A12,0),Jan2005.355,Feb2005.355,etc.,etc.,Dec2005.355))
>
>Biff
>
>"DMDave" <[email protected]> wrote in message
>news:[email protected]...
>> Hi Biff,
>> The named range is just a named range
>> =NS2005!$C$9,NS2005!$C$17,NS2005!$C$25,NS2005!$C$33,NS2005!$C$41
>>
>> For some reason Excel isn't recognizing the result of the indirect as
>> a named range. No matter what I do it just returns a #REF.
>> Im stumped.
>> If I just use =SUM(Jan2005.355) it does return the correct answer but
>> I have to put in a variable to be able to change the month from Jan to
>> Feb to Mar etc...
>> Is there a possible alternative solution?
>>
>> Thanks
>> Dave
>>
>> On Sat, 6 May 2006 21:50:50 -0400, "Biff" <[email protected]>
>> wrote:
>>
>>>Hi!
>>>
>>>Is the named range just a named range:
>>>
>>>Jan2005.355 refers to:
>>>
>>>=Sheet1!$A$1:$A$10
>>>
>>>Or, is the named range really a named formula (dynamic range):
>>>
>>>Jan2005.355 refers to:
>>>
>>> =OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A))
>>>
>>>If it's the latter, Indirect won't work.
>>>
>>>Biff
>>>
>>>"DMDave" <[email protected]> wrote in message
>>>news:[email protected]...
>>>> Is there a way to identify to excel in that the following formula:
>>>> =SUM(INDIRECT(C1&"2005.355")),
>>>> this part INDIRECT(C1&"2005.355") is a named range in the wb?
>>>>
>>>>
>>>> Where c1=Jan
>>>> and,
>>>> the named range is Jan2005.355.
>>>>
>>>> This refers to the same question I posted a few minutes ago if it will
>>>> help understand the question more.
>>>>
>>>> Thanks
>>>> Dave
>>>
>>
>

You're welcome. Thanks for the feedback!

Biff

"DMDave" <[email protected]> wrote in message
news:[email protected]...
> Biff, Thanks so much. That worked great!
>
>
>
> On Sun, 7 May 2006 00:01:36 -0400, "Biff" <[email protected]>
> wrote:
>
>>>The named range is just a named range
>>>=NS2005!$C$9,NS2005!$C$17,NS2005!$C$25,NS2005!$C$33,NS2005!$C$41
>>
>>That still won't work with Indirect because it's not a contiguous single
>>range reference . To Indirect, it's 5 separate arguments.
>>
>>About the only thing I can think of is something like this:
>>
>>=SUM(CHOOSE(MATCH(C1,{"Jan","Feb"},0),Jan2005.355,Feb2005.355))
>>
>>This could be quite long if you have 12 named ranges, 1 for each month.
>>
>>You can make it a little shorter by creating a named formula for the
>>months:
>>
>>Months
>>Refers to: ={"Jan","Feb","Mar",etc., etc., "Dec"}
>>
>>Or, list the months in a range of cells:
>>
>>A1 = Jan
>>A2 = Feb
>>A3 = Mar
>>etc
>>A12 = Dec
>>
>>Then:
>>
>>=SUM(CHOOSE(MATCH(C1,Months,0),Jan2005.355,Feb2005.355,etc.,etc.,Dec2005.355))
>>
>>Or:
>>
>>=SUM(CHOOSE(MATCH(C1,A1:A12,0),Jan2005.355,Feb2005.355,etc.,etc.,Dec2005.355))
>>
>>Biff
>>
>>"DMDave" <[email protected]> wrote in message
>>news:[email protected]...
>>> Hi Biff,
>>> The named range is just a named range
>>> =NS2005!$C$9,NS2005!$C$17,NS2005!$C$25,NS2005!$C$33,NS2005!$C$41
>>>
>>> For some reason Excel isn't recognizing the result of the indirect as
>>> a named range. No matter what I do it just returns a #REF.
>>> Im stumped.
>>> If I just use =SUM(Jan2005.355) it does return the correct answer but
>>> I have to put in a variable to be able to change the month from Jan to
>>> Feb to Mar etc...
>>> Is there a possible alternative solution?
>>>
>>> Thanks
>>> Dave
>>>
>>> On Sat, 6 May 2006 21:50:50 -0400, "Biff" <[email protected]>
>>> wrote:
>>>
>>>>Hi!
>>>>
>>>>Is the named range just a named range:
>>>>
>>>>Jan2005.355 refers to:
>>>>
>>>>=Sheet1!$A$1:$A$10
>>>>
>>>>Or, is the named range really a named formula (dynamic range):
>>>>
>>>>Jan2005.355 refers to:
>>>>
>>>> =OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A))
>>>>
>>>>If it's the latter, Indirect won't work.
>>>>
>>>>Biff
>>>>
>>>>"DMDave" <[email protected]> wrote in message
>>>>news:[email protected]...
>>>>> Is there a way to identify to excel in that the following formula:
>>>>> =SUM(INDIRECT(C1&"2005.355")),
>>>>> this part INDIRECT(C1&"2005.355") is a named range in the wb?
>>>>>
>>>>>
>>>>> Where c1=Jan
>>>>> and,
>>>>> the named range is Jan2005.355.
>>>>>
>>>>> This refers to the same question I posted a few minutes ago if it will
>>>>> help understand the question more.
>>>>>
>>>>> Thanks
>>>>> Dave
>>>>
>>>
>>
>