1

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)))
0

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")
  • I'm sorry, I don't understand why an IF statement won't work. I'm trying to learn and would be grateful for more detail. I think I understand your filter suggestion, but it definitely makes the formula long. Seems like there has to be an easier way to do this. Also, I'm confused because the location "Brentwood" is found in Column C, not B in Form Responses 1. Also can you tell me why you used the range W2:AB in your filter? I would have thought it would be A2:AW. I appreciate any help! – Amy J Jun 13 '16 at 22:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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