I have rows where I need to sometimes cut and paste them into a different part of the sheet. However, there is a sum formula that sums the values in the original part. When I cut the row, the sum formula changes because that row was moved.

Is there a way to prevent the sum formula from changing when I cut the row?

Sum Formula: = SUM($A$1:A$10)
When I cut row A1 and place in below row 12, the sum formula changes to = SUM($A$2:A$10)


1 Answer 1


indirect() uses a static string to provide a reference to a range. it will not be modified by any of the features that assist in updating formulas when the sheet structure changes. It will solve this problem but it might be the source of others later. (insert Spiderman quote)



not that that explains this use of it well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.