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
=Sheet1!A3
=Sheet1!A4
After the third response the second sheet looks like this:
=Sheet1!A1 => Foo
=Sheet1!A2 => Bar
=Sheet1!A4
=Sheet1!A5
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?
Best Answer
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.