Editing a spreadsheet that has a cumulative sum column

English
#1

I have a spreadsheet into which I have entered a series of transactions, and alongside each transaction amount I have a formula which gives the running total.

Lets say, at some point in the future, I notice that the order of the transactions is incorrect and I need to move one of them up to its correct position. So I insert a blank row at the appropriate point and I can then copy the transaction that is in the wrong place into this blank row, and then delete the original row. This works fine.

However, I had expected to be able to MOVE the transaction into the blank row by dragging, rather than COPY it and delete the original. When I MOVE the transaction, the relative reference does not update.

For example if row 4 has a cumulative total formula of =SUM($B$2:B4), and row 7 has a cumulative total formula of =SUM($B$2:B7), and I want to move the current row 7 transaction into the position currently occupied by the row 4 transaction, if I use “copy and delete”, the formula copied from what was row 7 has its formula correctly changed to =SUM($B$2:B4) to reflect its new position.

If, instead I MOVE the row, then I end up with the formula =SUM($B$2:B8) in row 4. The relative reference has not been updated. (The reason it is now 8 not 7 is because it was updated when I added the new row at 4.)

Obviously it is no great hardship having to use “copy and delete”, but I have to do this row rearrangement quite often, and I find myself using the MOVE method by mistake and then wondering why my spreadsheet is wrong.

Does anyone have any suggestions?

#3

The same if do you hold Alt while dragging the row to the new position.

#5

Hi LeroyG. Thanks for that. Using Alt-Drag does something weird to the reference, but using it in combination with erAck’s solution saves me the step of making a blank row for the destination and closing up the blank row left behind!

1 Like
#2

Normal behaviour. When a formula cell is moved to a different position, it is expected that the formula does not change.

Use named expressions. E.g. on cell C2 (or in whatever column your =SUM($B$2:...) formula resides) hit Ctrl+F3 (Sheet → Named Ranges and Expressions → Manage…) and Add,

  • Name: refname
  • Range or formula expression: $B$2:B2

Add, OK. Then in cell C2 enter =SUM(refname) and pull / copy-paste down. The named reference will keep its position dependent relative reference wherever it is used, also when moved.

2 Likes
#4

Thanks erAck. That works fine. I never would have worked that out for myself!
As I had simplified my problem for posting the question, I now have to get my head around fitting your solution into my more complicated problem!

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.