When I add a new row for the current reporting month to the top (becomes row 9), my absolute cell references in my formulas on rows 3–5 change to move down a row, now referencing rows 10 and 11, instead of rows 9 and 10. Which I'm sure is intended behaviour for the spreadsheet.
However I always want the data in rows 9 and 10 to be compared (the most current two months).
Eg at the end of August I will add a new row 9 for August 2020, and I want to compare August with July, not continue to compare July with June. Seems intuitive / straightforward enough, but somehow need to work around how Google Sheets treats these references.
Best Answer
Kudos to {Stephen,G} for this simple answer on the Google Docs Editors Community.
The
INDIRECTfunction was what I needed. I had tried it myself previously but I made the simple error of not wrapping the cell reference in double quotes (not sure why that is exactly, but that's how it goes!)Eg
INDIRECT("$B$9")