Google Spreadsheet changing cell references on its own

Question

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?

Answer 1

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.

Answer 2

EDIT to include the essential information from the link, original answer here at bottom.

The first two methods do not solve my problem, as cells referred to in formula continue to update the reference as extra records are added from the form. Method 3 answers my problem as specified in the question....

Method 1: Duplicate the sheet. Click on the little arrow on the tab and choose duplicate. This allows you to change the data, move columns, write formulas, delete data you didn’t want, etc… without messing up the original data.

Method 2: Link to the “Form Responses” information on another tab. If you create another tab and put =’Form Responses’!A1 into the new sheet, what is in cell A1 from the form responses tab will show up.

For me linking the sheets never worked. I saw either errors such "Google failed to link these sheets". Seems to be a user authentication problem. I don't understand why I need to link sheets in the same spreadsheet, seems to be an oversight on Googles.

Method 3: Use =importrange(“spreadsheet key”,”tab range”) in cell A1 on the new empty sheet, where spreadsheetkey is the long number in the URL. It will look something like this:

=importrange(“0AiqrAI5UxSJNdGs2a1dZbkRuMWozak5Sanc5Wm95LWc”,”Form Responses!A1:C300“)

Use the extra range (e.g.3000)to accommodate new answers as they come in.

For me, this method is now working.

PREVIOUS ANSWER

The answer seems to be here

I am having trouble with method 2 linking the sheets. Google access is denied between sheets in the same spreadsheet! Anyway solved in method 3.

Answer 3

It's extremely unlikely that this is the spreadsheet changing things "on it's own" - highly likely that it's operating as designed.

Firstly, how did you make the exact copy in Sheet2?

If you used an =(cell-ref) formula in every single cell, and then had an external process (eg your Form) add some more rows to the bottom of the existing data table, then the subsequent rows will be moved down.

You can see an example of it in this spreadsheet: when you enter a new response in this form, the data in the subsequent rows moves down.

The solution is to re-design your spreadsheet so it doesn't matter that the data in the form-results rows is ever-expanding. I would typically put formulas etc that operate on it into another sheet.