Google-sheets – Google form wrongly delete rows in second sheet

formulasgoogle sheetsgoogle-sheets-arrayformulavlookup

I copied a previous question here since it's exactly the same as what I want to ask:

I created a form in Google Docs. Then I created a second sheet to pick
and process data from the first sheet using formula. 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 first empty row, the number it refers to is increased
one number too far. Therefore the response will not be automatically
generated in the new row of the second sheet. If I copy the formula in
the old row then post to new empty rows, the new response will then
show. But I need to do that manually instead of it generated
automatically. 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

Notice instead of A3, it became A4. 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. If I remember it correctly, this was working just fine until I removed all rows in the first sheet after testing.

Anyone knowing how to solve or work around this?

Best Answer

When you move formulas around, or the cells they pull values from, Sheets does its best to intelligently preserve or update those links. You said "I'm not deleting rows myself in the second sheet." That's true.

Instead, you're accidentally doing the opposite—creating rows in the first sheet—by having a form attached to it. Your second sheet's third cell conceptually pulls from "the first blank row on Sheet1," currently A3. But when a form response comes in, all the rows move down to accommodate the new row of responses, so your same-old "first blank row on Sheet1" is now in A4. The second sheet reference to that cell updates appropriately.

The fix is generally to refer to entire arrays of data. Use direct unbounded ranges like =Sheet1!A:A or array-expectant formulas like FILTER, SORT, UNIQUE, ARRAYFORMULA, QUERY, etc.

Follow some tutorials (1, 2, 3) or help articles. If you get stuck or end up with pointed technical questions, edit your question here. Or much better, share your sheet and include a link when you write up a new question.

Oh, we need to take a moment for a special caution about what you're doing in the first place.

Whenever reflecting data from one location again elsewhere, individually or by using any formula that has a result array (that is, multi-cell output, so; SORT, UNIQUE, FILTER, ARRAYFORMULA, etc.), that formula's output is both dynamic and one-way from input to output.

One Way. You cannot edit the output of a spreadsheet formula. If you type some text or a number over a formula result, be it one cell or one value out of five thousand, the formula is no longer functioning. You've just got the one value you entered. It may be the right value even, but it's merely what you typed, not a live formula result anymore. This also applies if you copy a result and paste as plain text. If you need to alter the result of any formula, you must either change the formula itself to match your actual need, or directly alter the source values.

Example: if someone has a typo in their form entry, if you type over a spot where it's displayed on Sheet4 in a simple one-cell formula or a huge multi-column report, the formulas are broken.

The takeaway: fix it in Form Responses. Since any formulas that read entries are 'live', when you edit a Form Responses row, the second sheet report using it will recalculate and update as well.

Dynamic. Any spreadsheet formula doesn't care what you type near its output, and cannot help you keep data organized or associated.

Example: imagine the nightmare of having entered new data next to an automatic list (e.g. in a blank column right of your second sheet's reflection of Sheet1), but then a couple rows of source values are intentionally and correctly deleted (e.g. two form responses are invalidated). The source values all move up to fill the gap as you delete the rows. Also, the live formulaic output all shifts up a couple rows. But since your comments are not part of the automatic list, they stay put and are now misaligned.

The takeaway: keep related data together. You are free, for example, to add new columns next to form-linked "Form Responses" (or "Sheet1") columns. They would be sorted, moved, or deleted as rows and always stay together. In kind, any formulas that were reading and displaying those new columns elsewhere would also reflect the changes correctly.

Mitigating both problems, also perhaps consider UI cues to not edit a formula-result area nor work next to it. Deleting extra columns next to reports can discourage "margin notes". Sheet protection, a light gray background, or white cell outlines, or just a label row above the real headers can remind all the file's users that those columns are automatic.

Related Topic