Need formula to lookup a named range

[DMDave]

Hi, I'm trying to figure out a formula to lookup a named range based
on the month (e.g. Jan) entered in a cell on the sheet.

For instance:

on sheet 'Sales Page, in cell C1 I will enter the month (Jan).

I have a named range on sheet 'NS2005 called "Jan2005.355".

I want a cell in 'Sales Page to look at C1 and find the named range
that has the same first three letters of the month and return the sum
of the range.

e.g.
in cell C1 of 'Sales Page is Jan and it finds and returns the sum of
named range Jan2005.355

or if C1 is Feb it finds and returns the sum of named range
Feb2005.355.


I tried using=CONCATENATE(T($C$1),2005.355) but apparently the result,
while returning the correct name, Jan2005.355, does not work as a
named range or excel doesnt recognize it.

Any help will be greatly appreciated.

Dave

 

[Pete_UK]

Try this:

=SUM(INDIRECT(C1&"2005.355"))

Hope this helps.

Pete

 

[DMDave]

Thank you Pete for the quick response.. however when I entered your
formula it returned a #REF error. Does the named range have to be on
the same Sheet as the formula for it to work?

Thanks
Dave

 

[DMDave]

Oh BTW, upon running the function wizard I discovered that the result
was 'volatile' if that helps.
Thanks
Dave

 

[Pete_UK]

It worked for me!

Did you have "Jan" in C1? Are you sure the named range is Jan2005.355
and not spelt slightly differently?

As long as there is only one named range Jan2005.355, then it should
not matter if it is on a different sheet. However, to be on the safe
side, you could change the formula to:

=SUM(INDIRECT("Sheet1!"&C1&"2005.355")),

assuming the named range is in the worksheet Sheet1.

Yes, I know that the function is volatile.

Hope this helps.

Pete

 

[DMDave]

Hey Pete,
Answer to Question 1 is Yes. Question 2 is Yes.
I tried the second formula you sent
=SUM(INDIRECT("Sheet1!"&C1&"2005.355"))
and it didnt seem to work.

Are there possible alternatives to provide the result I need?

Thanks,
Dave