referring to a dynamic range

[bolo]

Hi all,

I was wondering if somebody can help.

I have a dynamic name called "crap"

In cell A1 i have the string "crap-2"

This cell will vary and look up a different table.

I have tried the formula = sum(indirect(left(A1,4)) but this returns a #ref error.

I have had a quick invistigation and if crap referred to a static range, the function above works. So, is there any way i can make this work with a dynamic range in a name?

Thanks

Bolo

P.S. I am using XL2K

 

[NBVC]

You need to include the ! to refer to a sheet name and also the cell references which need to be summed.

Try, adjusting A1:A5 to your range to sum in the reference sheet.

=SUM(INDIRECT(LEFT(A1,4)&"!A1:A5"))

 

[bolo]

NBVC

the whole point is that "left (A1,4)" already refers to a named range. Thus I do not need the cell reference to the range. In fact the named range is dynamic, so that the range will vary, and not be fixed to A1:A5 the whole time. If it was fixed then this would be a very simple problem.

Regards

bolo

 

[NBVC]

Sorry, I misunderstood and assumed that it was a sheet reference,

But I tried your exact situation.

I named a range: "crap", in my case C2:C10 on Sheet1

I typed crap-2 in cell A1 of sheet 2

And then typed =SUM(LEFT(A1,4)) and I got the expected result Sum(Sheet1!C2:C10).

Am I still confusing it?

 

[bolo]

NBVC,

What you have created is a static range i talk about here:

I have had a quick invistigation and if crap referred to a static range, the function above works. So, is there any way i can make this work with a dynamic range in a name?

My problem occurs with a DYNAMIC range....

Bolo

 

[NBVC]

According to some Excel sites, you can't get it to work using the Indirect function. This site suggest creating a UDF or additional Named functions. You can check it out if you wish:

http://www.dicks-blog.com/archives/2005/03/01/indirect-and-dynamic-ranges/

I came up with this simple formula,though, that seems to work,

=IF(LEFT(A1,4)="crap",SUM(crap),"").

Can that work for you?

 

[nbrcrunch]

bolo, I think nbvc's last post had you what you wanted.

=sum(crap)-2

Merely naming a range doesn't tell it what you want to do with the range.

crap-2 would translate to merely C2:C10-2 which is not valid syntax.

however,

sum(crap)-2 would translate to sum(c2:c10)-2 which is valid as is...

average(crap)-2
max(crap)-2
min(crap)-2

However, when you have so much crap in your life, you need a change of scenery.

 

[Aladin Akyurek]

Hi all,

I was wondering if somebody can help.

I have a dynamic name called "crap"

In cell A1 i have the string "crap-2"

This cell will vary and look up a different table.

I have tried the formula = sum(indirect(left(A1,4)) but this returns a #ref error.

I have had a quick invistigation and if crap referred to a static range, the function above works. So, is there any way i can make this work with a dynamic range in a name?

Thanks

Bolo

P.S. I am using XL2K

If you download and install the latest version of the morefunc.xll add-in, you can invoke:

=SUM(EVAL(LEFT(A1,4))