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