Thursday, October 25, 2012

yogi_Query Sheet1 For User Specified Criteria And Present Resulting Table In Sheet2


                                           Google Spreadsheet   Post  #827
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 25, 2012
user esmithnola said: (http://productforums.google.com/forum/?zx=r3nme9qg99cb#!category-topic/docs/spreadsheets/fN5cQZeLs24)
Query returning all matches in a single cell, please help!
If you want to see my sheet and formulas, you can access/edit at 

I've written a query where my Sheet2 goes to look in Sheet1 and returns columns A, B, AV, CC when column AH or AJ match a value input in B3 on Sheet 2
=QUERY('Sheet1'!A:CC;"select A,B,AV,CC where C=11 and (AH <='"&$B$3&"' or AJ <='"&$B$3&"') order by B")

It works, but it returns all the values in a single cell instead of a new row for each match (normal behavior). Any thoughts? I've tried all sorts of things, but still the same behavior.

(Btw, in case you're wondering I'm trying to make a sheet where I can automatically pull certain students based on input criteria. All the information on those sheets is just made up.)
--------------------------------------------------------------------------------------------------
following is a solution to the problem


2 comments:

  1. hello Yoggi,
    Just two questions with the aim and the need of the & symbol in the query formula. Another question is the effect of the ,1 at the end of that query formula.
    Thanks for your teach!
    PS : Romain is coming soon in Reunion island at home, smoking brains!

    ReplyDelete
  2. Hi ABSALON:

    & in the formula using the QUERY function is being used to say that B3 is a cell reference and not a literal in this case.

    use of 1 at the end in the QUERY function is to signify that one row in the queried range is to be considered as header row.

    Oh Ya, Romain is Great ... I can't wait to hear about his teaching about Google Apps Script -- please do tell me all the details.

    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete