0

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.

0

Kudos to {Stephen,G} for this simple answer on the Google Docs Editors Community.

The INDIRECT function 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")

| improve this answer | |
  • Hopefully that helps some others. – Drewdavid Aug 6 at 20:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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