Google-sheets – Querying multiple ranges from form responses to new sheet

google sheetsgoogle-sheets-query

Recently, someone on this forum put together a formula for me. It works on a sheet pulling responses from a google form. I'm trying to model a new one after that but I'm having trouble. The working formula can be found in the blue box here:
https://docs.google.com/spreadsheets/d/…

If you toggle between the sheet titled BWSU2016 and Form Responses 1, you can see the way formula pulls the info from each submission to multiple rows within BWSU2016.

The spreadsheet with which I need help is here: https://docs.google.com/spreadsheets/d/…

The ranges I need to pull are indicated within the formula below. In particular, I am attempting to add an IF statement so that only form responses which indicate "Brentwood" in column C will be queried.

=if(('Form Responses 1'!C2:C="Brentwood"),sort(query(
 {'Form Responses 1'!D2:G,'Form Responses 1'!I3:N;
  'Form Responses 1'!D2:G,'Form Responses 1'!P3:U;
  'Form Responses 1'!D2:G,'Form Responses 1'!W3:AB;
  'Form Responses 1'!D2:G,'Form Responses 1'!AD3:AI;
  'Form Responses 1'!D2:G,'Form Responses 1'!AK3:AP;
  'Form Responses 1'!D2:G,'Form Responses 1'!AR3:AW},
 "Select * where Col5 <>'' "),D1,if(F1="Z > A",0,1)))

Best Answer

You wrote '"Brentwood" in column C', but in the actual form, the location (Brentwood) is in column B. Anyway, since location is not returned by the present query, it is too late to try to narrow the results down after running it.

But you can filter before querying: replace each range such as

'Form Responses 1'!W2:AB

by 

filter('Form Responses 1'!W2:AB, 'Form Responses 1'!B2:B = "Brentwood")

This will make the query even longer; it may help to rename "Form Responses 1" to something like "FR". Then the formulas are a lot shorter: 

filter(FR!W2:AB, FR!B2:B = "Brentwood")
Related Topic