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

 

[Robert_Steel]

Have a look at the Indirect() function in help

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

hth RES

 

[Guest]

I don't believe you are truly using Excel's consolidation functionality.
Click on the cell where you want the answer to be. Then follow the menu path
Data / Consolidate. Choose the appropriate function (Sum). If there is
anything in the All References section, delete them. Then 1 at a time add
the references you need (X1!$A$1, X2!$A$1, X3!$A$1, etc....). Then ENSURE
YOU CHECK the create links to source data box. That way when the values
change so will your answer. You can uncheck both the top row and left column
check boxes. Click OK and your answer should appear. Then if you go into
sheet X1 for example and add some rows before row 1 making cell A1 now
something like A5 your answer will still reflect the proper answer. it will
know to look at X1!A5, X1!A1, etc....

Play around with the data consolidate functionality and review Excel's help
on it.

Hope this helps.

Bill Horton