Google-sheets – Google Spreadsheet changing cell references on its own

google sheetsgoogle-forms

I have created a Google Form with an attached spreadsheet. Within that spreadsheet I have created several sheets to sort the answers to my liking.

Sheet 2 is just an exact copy of all the original answer cells in Sheet 1, but with some columns added for further explanation. It works fine for a bit, but twice now the sheet has changed cell references on me vertically. It references fine from A2-A30, but then makes a jump which makes the next reference in Sheet2 cell A31 become Sheet1!A45. This goes for all columns horizontally, all the way up to column HY, which is my last.

Any ideas?

Best Answer

I think I found a solution based on the INDIRECT formula.

Assuming to have the spreadsheet "Responses" with the raw inputs from the form, and the spreadsheet "Processing" to rework the answers, you have to create a third spreadsheet "References".

This third spreadsheet will contain, as plain text, the references you need. For example, you could create a matrix to have all the values from Responses!A1 to Responses!Z100.

So, in "Processing" instead of referring to Responses!A1 in a cell (since, as said, this will be shifted when adding a new response from the form), you have to use =INDIRECT(References!A1).

Not sure the explanation is clear, but it works perfectly.

Related Topic