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
hello Yoggi,
ReplyDeleteJust 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!
Hi ABSALON:
ReplyDelete& 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