Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
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)
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)
=sum(INDIRECT("a1:a10"))
https://support.google.com/docs/answer/3093377?hl=en
not that that explains this use of it well.
