referring to a dynamic range

bolo

Active Member
Joined
Mar 23, 2002
Messages
423
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
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

Active Member
Joined
Mar 23, 2002
Messages
423
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

Active Member
Joined
Mar 23, 2002
Messages
423

ADVERTISEMENT

NBVC,

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

bolo said:
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
 

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
bolo said:
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,118,286
Messages
5,571,313
Members
412,382
Latest member
Langtn02
Top