2

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)))
1
  • Show what you have tried including sample data, what you found from this site, from the official resources for end-users and why they didn't meet your needs. Please edit your question to provide the requested details. Once you have done that it can be reviewed, reopened, and answered. If you need further help, please read first Guides to ask a [google-sheets] question.
    – Rubén
    Apr 25 at 18:46

1 Answer 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")
1
  • 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, 2016 at 22:11

Your Answer

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

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