Say I have a Google Sheets or Microsoft Excel spreadsheet where the formula in cell A1 is “=SUM($A$5:$A$100)”. Assume there is data is cells A5 through A10, but nothing (yet) in A11 through A100.
Now insert a row above A5. This moves the data from the 5th row down and A5 is now blank. Enter something in A5. There is now data in A5 through A11. But the formula in A1 has been updated to “=SUM($A$6:$A$101)” so doesn’t include the new cell.
“Absolute reference” markers $ were used, so I had expected the formula to be fixed.
Is there a way to force the formula in A1 to be constant regardless of whether rows are inserted or deleted lower down?
Ideal would be a general solution that applies to both Google Sheets and Microsoft Excel. If the solutions are unique to each, please note that. Thanks!
