How do you Identify text as a named range in excel

[DMDave]

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

 

[Pete_UK]

Dave,

I've responded to your other post.

Pete

 

[Biff]

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]

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

 

[Biff]

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]

Biff, Thanks so much. That worked great!

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

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Biff, Thanks so much. That worked great!