text as an argument of SUM function

[katarina07]

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

 

[erik.van.geit]

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

 

[GlennUK]

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.

 

[katarina07]

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

 

[erik.van.geit]

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