0

In Google Sheets I have spreadsheet containing two sheets. The first, 'main sheet', contains formulas that reference data stored in the second sheet, 'Form Responses 2', that stores Google Form responses.

I have written my formulas in 'main sheet' to include rows from 'Form Responses 2' that are not yet populated in anticipation of future submissions. For example where only the first 11 rows of 'Form Responses 2' are populated, I may have the following formula =INDEX('Form Responses 2'!C12) in 'main sheet' anticipating the next submission.

However when a new form response is received, my existing formulas don't automatically include the new data, instead the formula that referenced Row 12 is updated to reference the Row 13.

What I've tried

  • I've tried QUERY as well as ARRAYFORMULA, the latter suggested in a similar question How to get new Google Form responses to take on non-form functions in Google Sheets
  • I don't think I need VLOOKUP() or HLOOKUP(), as I'm not searching for values in the other sheet.
  • I looked at the documentation for FILTER, and haven't yet tried it, but it doesn't seem like it would be useful.
  • I tried absolute references such as =INDEX('Form Responses 2'!$C$12) but then the references don't update when I fill them down and regardless, it still exhibits the same problem.
  • I tried adding :C to the cell reference in response to comments, such as =INDEX('Form Responses 2'!C12:C), but this created an error #REF!, which had a tooltip stating: "Error Array result was not expanded because it would overwrite data in C13."
  • This same error occurs if I try =ARRAYFORMULA(INDEX('Form Responses 2'!C12:C)).

How can I get 'main sheet' to update automatically when a new form is submitted?

Example Tables

In the tables I've skipped rows 2-10 for simplicity.

1.  'Form Responses 2'

1a.  Initially
I have form responses up to and including Row 11 but none in Row 12, onwards:

C
1 What turn was reached?
11 70
12
13

1b.  After a form submission
Now there are form responses up to and including Row 12 but none in Row 13, onwards:

C
1 What turn was reached?
11 70
12 100
13

2.  'main sheet'

2a.  Initially
I have formulas in Rows 11 to 13 but only Row 11 returns a value because Rows 12 and 13 of 'Form Responses 2' are empty.

D                               
1 *Turn Count
11 =INDEX('Form Responses 2'!C11) = 70
12 =INDEX('Form Responses 2'!C12) =
13 =INDEX('Form Responses 2'!C13) =

2b.  New Submission
I would expect Row 12 to return 100 from 'Form Responses 2'!C12 but instead the formula in Row 12 and subsequent rows are shifted down one row.

D                               
1 Turn Count
11 =INDEX('Form Responses 2'!C11) = 70
12 =INDEX('Form Responses 2'!C13) =
13 =INDEX('Form Responses 2'!C14) =
1
  • 1
    Thank you very much. Your rewrite is significantly clearer than any formulation of my question that I composed. I think your table 1b. After a form submission isn't rendering properly, so I'll fix that. Just so you know, if I can't figure out how on my own, I'll be asking another question to Stack Exchange about Google Sheets, as now I have to rewrite several formulas. But this is an improvement! Thank you again; I've learned a lot. edit: removed an erroneous word.
    – Fysyx
    Commented Mar 24 at 22:16

1 Answer 1

0

Array Formula

An array formula in 'Main Sheet'!D11 will dynamically add new values to 'Main Sheet'!D11:D as new form submissions populate values in 'Form Responses 2'!C11:C.

Formulas

There are many approaches to array formulas you can use. Here are three examples using FILTER, QUERY, and INDEX

FILTER

=FILTER('Form Responses 2'!C11:C, 'Form Responses 2'!A11:A<>"")

# The following is equivalent if C is a required field 
=FILTER('Form Responses 2'!C11:C, 'Form Responses 2'!C11:C<>"")
  • Spill range * height matches only those rows containing form submissions.
  • FILTER condition using Column A is a reliable indicator of a form record.

QUERY

=QUERY('Form Responses 2'!A11:C, "SELECT C WHERE A IS NOT NULL")

# If C is required
=QUERY('Form Responses 2'!C11:C, "WHERE C IS NOT NULL")
  • Spill range * height matches only those rows containing form submissions.
  • QUERY condition using Column A is a reliable indicator of a form record.

INDEX

=INDEX('Form Responses 2'!C11:C<>"")
  • Spill range * for INDEX includes the entire range Sheet'!D11:D`

* Spill Range & #REF! Errors

The cell containing a formula can only house one member of an array. If a formula results in an array whose dimensions are greater than 1x1 it will spill into as many adjacent cells as necessary to house it. This spill range must be empty or Sheets will return an error. Regardless of how many members of the spill range are not empty,Sheets will only note the cell reference for the first one that triggers the error:

#REF!
Array result was not expanded because it
would overwrite data in [A1].
7
  • 1
    Thank you Blindspots for your response. I think I used an example that might have mislead you. Concatenating values from the responses sheet on the main sheet was a simplification of only one task where I was having difficulty. My problem is properly extracting the values from the sheet that stores each new form response in a way that does not require user input. I 1) looked at FILTER() documentation; it will not help me here. 2) Tried adding ":A" after the cell index (eg A11:A), but got "An array value could not be found" with new form response data. I will hone my example. Thank you.
    – Fysyx
    Commented Mar 23 at 0:55
  • If you can provide a sample of some data in markdown format and a formula so we better understand your challenge. If you want to share a spreadsheet anonymously you can use Blank Sheet Maker It's run by a Google Group and uses your email only to send you a link to the sheet then it's discarded.
    – Blindspots
    Commented Mar 23 at 1:00
  • I updated the example to specify (what I think is) the precise problem—the concatenate thing might have been merely a distraction; if that's not helpful, I can elaborate the example, making it more close to my actual use case. The data isn't sensitive (I'd just need to edit out some people's real names) so if a more detailed example would be more helpful to you or other readers, I can provide this. Thank you again for your time.
    – Fysyx
    Commented Mar 23 at 1:26
  • 1
    I think this communicates the problem, which—as I understand it—is caused by the formulas changing when data is added by receiving form responses. I have attempted to show the problem by trying to indicate change in time. I think t=2 is, strictly, more than minimally necessary, but might be helpful. I still have the more complete markdown table prepared, if this update is still insufficient. Thank you again for your patience.
    – Fysyx
    Commented Mar 24 at 15:07
  • 1
    To be clear: I edited the body of my question to use these tables. I can use the Blank Sheet Maker you linked above, or other tools, if I haven't effectively communicated my problem and these tools would be preferable.
    – Fysyx
    Commented Mar 24 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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