Web Applications Stack Exchange is a question and answer site for power users of web applications. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I created a form in Google Docs with quite some questions. For a good overview I created a second sheet to pick the important parts from the survey. There I'm also able to add extra columns to add human-processing information.

In my second sheet I use functions like =Sheet1!A1 to refer to the field in the first sheet. This works fine.

However it goes wrong when a new response comes in. In the second sheet, the references don't work as expected. When looking at the function in the last empty row, the number it refers to is increased one number too far. So, before the new response the second sheet looked like this (with two responses):

=Sheet1!A1 => Foo
=Sheet1!A2 => Bar

After the third response the second sheet looks like this:

=Sheet1!A1 => Foo
=Sheet1!A2 => Bar

I'm not deleting rows myself in the second sheet. It seems that Google Docs automatically deletes rows from the second sheet when inserting a new response in the first sheet.

Anyone knowing how to solve or work around this?

share|improve this question
I have exactly the same problem. It's very weird. – gtirloni Apr 1 '12 at 15:46
up vote 3 down vote accepted

I've been able to find a solution for my problem. Using =ARRAYFORMULA(Sheet1!A1:A) in the top of a column Google Docs will update the whole column, without the need for functions in each cell. Thus, deleted or inserted rows will not cause trouble anymore.

I found this answer (by brettathds) at the Google Support forums.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.