2

This question already has an answer here:

I have a Google Spreadsheet with two sheets. The first uses formulas to manipulate the second sheet row by row, and the second is fed by a Google form. My first sheet has formulas as such:

A2: int('Form Responses 1'!A2)
B2: 'Form Responses 1'!B2&" and "&'Form Responses 1'!C2

This is copied down 100 rows, where each row references the same row in the second sheet. However, when I submit the Google form, the formulas point to the next row, skipping entirely over the form data.

If I submit the form once:
A2: int('Form Responses 1'!A3)
B2: 'Form Responses 1'!B3&" and "&'Form Responses 1'!C3

Each row points to the next row in the second sheet. This repeats for every form submition, so after three submitions, the formulas look like:

A2: int('Form Responses 1'!A5)
B2: 'Form Responses 1'!B5&" and "&'Form Responses 1'!C5

I can also reproduce this problem if I insert a row above the row where the formulas point to. It seems they point to an actual row, and move their references when the row moves, rather than pointing to a certain row number and column number. How can I get out of this?

marked as duplicate by Rubén, serenesat, Eight Days of Malaise, jonsca Sep 8 '16 at 23:39

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

0

The described behavior occurs because a new row is inserted into the response sheet each time that a new response is submitted to the linked form.

In many cases it's possible to solve problem by changing the formulas to array formulas.

An array formula is a formula that returns an array of values instead of single values. Below are the adaptation of the formulas presented in the question.

=FILTER(int('Form Responses 1'!A2:A),LEN('Form Responses 1'!A2:A))
=FILTER('Form Responses 1'!B2:B&" and "&'Form Responses 1'!C2:C,LEN('Form Responses 1'!A2:A))

The above formulas use FILTER to avoid to populate the whole column with 0 or andand to keep the recalculation time as short as possible.

  • I did exactly that. When I tried your method, and the method in the linked answer, the row number in A2:A became A3:A. Thanks for trying, though – vikarjramun Sep 5 '16 at 22:43
  • One think that some of the "answers" including myself assume, is that the formulas are added after the first response is submitted. Please be sure that you add the response after at least one response was submitted. – Rubén Sep 5 '16 at 22:56
  • I thought I used form submitions, but instead I had manually typed in the spreadsheet. That fixed it. Thanks! – vikarjramun Sep 6 '16 at 23:11
  • Just wondering, what is the use of <>"" – vikarjramun Sep 6 '16 at 23:11
  • 1
    FILTER requires an array of TRUE/FALSE as the second argument in order to filter the values of the first argument. On this answer LEN() is used instead of <>"" . The first return an integer, if it's 0, it's parsed as FALSE, any value greater than 0 y parsed as TRUE. The second returns FALSE for any blank or empty string and TRUE for any cell that has any other value. – Rubén Sep 6 '16 at 23:19

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