2

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?

Improve this question
0

1 Answer 1

Reset to default
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.

Improve this answer
5
  • 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
    Commented Sep 5, 2016 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 - Volunteer Moderator - ♦
    Commented Sep 5, 2016 at 22:56
  • I thought I used form submitions, but instead I had manually typed in the spreadsheet. That fixed it. Thanks!
    – vikarjramun
    Commented Sep 6, 2016 at 23:11
  • Just wondering, what is the use of <>""
    – vikarjramun
    Commented Sep 6, 2016 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 - Volunteer Moderator - ♦
    Commented Sep 6, 2016 at 23:19

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