Changing cell reference

[EddieL]

Hi All

I have a total in a cell using the following formula:

=SUM(INDIRECT("W97:W"&ROW()-1))

The formula is written that way so that if extra rows are added after 97, the total would include the added rows. The problem I have is that I have a link from another spreadsheet which references this total, and when rows are added the reference to this total obviously changes. Is there any way of my link following the cell containing the total?

Any help would be much appreciated.

EddieL

 

[jim may]

One way..
You could assign a RangeName to the one-cell formula - "=SUM(INDIRECT("W97:W"&ROW()-1))" and Call it "MyTotal". That will keep track of it regardless os where it gets moved.

But change the Other Formula on the other spreadsheet to refer to "MyTotal" versus the Cell Reference/ HTH; Jim

 

[EddieL]

Hi Jim

Thanks for your suggestion, but the portion of ROW()-1 in the formula doesn't show which cell this value is in. It actually returns 0 not the value in the cell.

 

[jim may]

I'm surprised that doesn't work since you would be naming the cell with the formula that produces the results. Hummmm...
Thanks for your reply.
Jim