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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

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,122,585
Messages
5,597,029
Members
414,116
Latest member
sfullnet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top