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


--
katarina07
------------------------------------------------------------------------
katarina07's Profile: http://www.excelforum.com/member.php...o&userid=28385
View this thread: http://www.excelforum.com/showthread...hreadid=479726