text as an argument of SUM function

katarina07

New Member
Joined
Oct 27, 2005
Messages
20
Hello,
I have this function entered in a cell:

=SUM('X1:X30'!A1)

it sums values of all A1 cells from sheets between X1 and X30.
The problem is if I insert rows in these "X" sheets (because the structure of a sheet has to be changed for some reason),
the above mentioned formula (on a consolidation sheet) doesnt reflect this change, it still references A1, even if I shift this cell too.

I had an idea I could make it like:

=SUM("'X1:X3'!A" & ROW())

so that the range inside SUM changes dynamically, but the SUM doesnt process this text value and returns #VALUE!.

Anybody has idea how to make SUM recognize this argument, may be through some conversion or making a range reference out of this text value.

Thanks, Katarina
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Hello, katarina07
Welcome to the Board !!

try this
click tab X1
shift-click tab X30
right-click first line
choose insert on each sheet
line is inserted

your formula will update to =SUM('X1:X30'!A2)


kind regards,
Erik
 
Upvote 0
You shouldn't have a problem

Hi Katarina,

you say:
The problem is if I insert rows in these "X" sheets (because the structure of a sheet has to be changed for some reason),
the above mentioned formula (on a consolidation sheet) doesnt reflect this change, it still references A1 ...

If you insert rows into these sheets by grouping all the relevant sheets first, and then inserting rows, the formula on your consolidation sheet will adjust it's formulae automatically.
 
Upvote 0
I see,
grouping all sheets and changing the structure on all of them at once works.
The problem is, that is a big consolidation file and we simply wanted to make sure that no matter how the rows were inserted,
the cell A3 e.g. on the consolidation sheet will always sum up A3s on partial detail sheets.

We tried things like:

1. i thought if =SUM('X1:X3'!A1) works, also

SUM('X1:X3'!OFFSET(Sheet2!$B$3;ROW()-1;COLUMN()-1))

would work, but it doesnt.

2. if I put

="'X1:X3'!A" & ROW()

in a cell (e.g. cell=A2) as a text, and then use =SUM(INDIRECT(A2)),

it doesnt work either.

Seems like it cannot be done directly on sheet and not in a macro.

Thanks, Katarina
 
Upvote 0
katarina07

to my sense you'll need an extra range refering to the cells one by one
B1 = X1!A1
B2 = X2!A1
etcetera
(when you will insert a line in sheet X2 you will get in B2 = X2!A2)
then sum that range
=Sum(B1:B30)


kind regards,
Erik
 
Upvote 0

Forum statistics

Threads
1,217,346
Messages
6,136,043
Members
449,981
Latest member
kjd513

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
Back
Top