Spreadsheet formulas mysteriously changing after file closure and automation

Other Questions Google Sheets
1

Hey everyone, I’m having a weird issue with my spreadsheet formulas. They keep changing on their own after I close the file and run an automation task. It’s driving me nuts!

Here’s what’s happening:

  1. I set up some simple formulas in my sheet. For example:

    =IF(AND(OtherSheet!D15>0,OtherSheet!D15<30), OtherSheet!B15, "")

  2. I copy this down to row 500, adjusting the row numbers accordingly.

  3. I double-check everything, save, and close the file.

  4. Later, I run my automation which adds new data to OtherSheet.

  5. When I open the file again, all the formulas have shifted! Row 15 now points to row 16, row 500 points to 501, and so on.

This messes up my whole workflow. The new data isn’t showing up where it should, and my follow-up automation fails because it can’t find the right info.

Has anyone run into something like this before? Any ideas on how to stop these formulas from shifting around? I’m completely stumped!

2

I encountered a similar problem a while back and found that the issue was due to the automation inserting new rows in a way that shifted all the cell references. In my experience, one effective solution was to adjust the formulas to use absolute references so that they don’t move when rows are added. I also switched to using named ranges for the critical data and, in some cases, used the INDIRECT function to maintain the proper cell references. Checking the automation script to ensure it appends data rather than inserting rows at the top was also key to resolving the issue.

3

ugh, that sounds super annoying! have u tried using structured references instead? like =Table1[@[Column1]:[Column2]] - those usually dont move around when stuff gets added. also maybe check ur automation to make sure its not inserting rows at the top by accident. good luck fixing it!

4

This sounds like a classic case of row insertion during automation. I’ve dealt with this headache before, and it can be quite frustrating. One approach that worked for me was using OFFSET and COUNTA functions to create dynamic ranges. For example, instead of referencing specific cells, you could use something like:

=OFFSET(OtherSheet!$B$2,COUNTA(OtherSheet!$B:$B)-1,0)

This formula will always reference the last non-empty cell in column B, regardless of how many rows are added. Combining this with INDEX and MATCH can create robust, shift-proof formulas. Also, double-check your automation script to ensure it’s appending data at the bottom rather than inserting rows at the top. These tweaks should help keep your formulas stable even when new data is added.