Formulas Referencing a Form Response sheet moves rows when a new row is inserted in the response sheet

Question

We have a spreadsheet that has formulas to calculate a couple of things based on a Google Form. When someone submits something via the Google Form, it inserts a new row into the Form Responses sheet. We created formulas that will look at that sheet, some data gets copied 1:1, some gets calculated, some uses the SWITCH function. However, the formulas keep moving when a new row gets inserted. 'Form Response'!B3 becomes 'Form Response'B4 for example, and increments even more when new Forms are submitted.

How do we make it, so the formulas don't break?

Answer 1

You may try either of these 2 options:

#1

• Create a new tab within the sheet
• Place a formula in Cell_A1 of this new tab pointing to the form responses tab as such ={'Form responses 1'!A:Z}. This essentially mirrors the repsonses tab now
• Now create/point all your formulas to this new tab data

#2

• Hardcode all your formulas using indirect function. Lets say your current formula is ='Form responses 1'!A10; Now it has to be =indirect("'Form responses 1'!A10")
• This can become cumbersome if you have too many formulas, so its advisable to convert them to arrayformulas OR map/lambda versions.