Google-forms – Auto-update other sheets

google-forms

Responses are automatically added to the sheet Form Responses. I want to apply certain formulas in a sheet called Student Answers. However, Student Answers doesn't auto-update from the submitted responses. Here are the formulas I am trying to use:

='Form Responses'!A1` etc.
=IF('Form Responses'!E6='Answers'!$E$2,1,0)

Answers is another sheet with an answer key.

=SUM(D5:I5)

this sums the numbers given in the previous formula.

Basically, I have created a quiz, and I want to grade the quiz on a separate sheet.

Here is the Sheets in question.

Best Answer

The solution was to use =ARRAYFORMULA. Here are my working examples:

  • =ArrayFormula('Student Submissions'!A:C) for basic copying of columns

  • =ARRAYFORMULA(If('Student Submissions'!D3:D='Student Submissions'!D3,1,0)) for IF statements

  • =ArrayFormula(mmult(D3:I;TRANSPOSE(sign(column(D3:I))))) for SUM

  • =ARRAYFORMULA(J3:J/6*100) for AVERAGE and percent

Note: I found these answers in the Google Product forums but cannot find the link again.

Related Topic