Running Calc 24.8.4.2 on an M1 iMac.
I have a sheet with a running total column and therefore each row has the formula “=R[-1]C+RC[-1]” in that column.
When I delete a row (right click - ‘Delete Rows (for unprotected cells)’, all subsequent rows have “#REF” in that column. I can fix this by copying down the last ‘good’ row formula into that column and it fixes things.
However, is there a way to not have the #REF errors come up in the first place?
Why are the references to the subsequent rows not still referencing the relative row/column cells?
(My logic is that this formula should reference the cell in the total column from the row above and then add the value to the left to give a running total. I’m using this form of cell reference as the sheet is created by a macro and I don’t want to have to work out the ‘B5’ style references for each new row.)
Susan
If the values are in column A, add =SUM(R1C1+RC[-1])=SUM($A$1:A1)] in the running total column, and fill down.
=SUM(R1C1:RC[-1]) (see @karolus comment below).
did you mean =SUM(R1C1 : RC[-1])
Yes.
This is the first time in so many years that I work with Excel R1C1 format, and I wrote the formula in Calc A1 format, and made the change to R1C1 (it worked) and copy from there.
Not sure what happened in between.
Thank you everyone.
I ended up with the solution:
=SUM(R2C3:RC3)
(using the actual column and row references in my sheet).
[Actually I had to modify a few other things as the top of the ‘running total’ column had the balance from the previous page - this is a monthly budget spreadsheet if that matters! - but that would require that the top row of the running total column to be different to all of the other rows; I moved the carry over balance to the top of the individual entry column and all is well.]
I’m left now with the 2nd question (which I admit is a bit hidden in the original post) as to why
=R[-1]C+RC[-1]
doesn’t work. I would have thought that it would always reference the new row above and the column to the left of the ‘current’ cell, even when the row above was deleted.
To avoid cell references bein linked to the referenced cell - and hence return an error when the linked cell no longer exists - you can insert the address as a string value, and use INDIRECT() to convert that string to a working cell reference:
=INDIRECT("R[-1]C")+INDIRECT("RC[-1]")
In some contexts this may be less portable. I cannot envision when that should happen, but if it does you can try to also build the address string by function (which is what I’d usually advise, but it may be unnecessarily convoluted in your case). You can then use OFFSET() to point to the correct relative cell.
=OFFSET(INDIRECT(CELL("address"));-1;0)+OFFSET(INDIRECT(CELL("address"));0;-1)
To avoid this most inefficient stuff I would prefer:
=SUM( INDEX(C;ROW()-1) ; INDEX(C[-1];ROW() )
or just sum up the column left up to current row:
=SUM( INDEX(C[-1];1) : INDEX(C[-1];ROW()) )
or better the formulas provided by @LeroyG 