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?


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.

| improve this answer | |

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.


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.

| improve this answer | |
  • 1
    While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. – ale Oct 12 '14 at 18:56

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.

| improve this answer | |
  • As seen in your example MaryC, formulae in the second sheet will also be pushed down. Typically one needs to use an array formula in this case (eg ='Form Responses'!A:B). – AdamL Aug 13 '14 at 22:34
  • Indeed. I spent some time looking for a solution with array formulae, but they also seems to push the subsequent content down. – MaryC.fromNZ Aug 14 '14 at 10:23

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.