Google Sheets Query – Cell Reference Guide

google sheetsgoogle-sheets-query

I'm trying to have my query reference a cell, but I keep getting errors:

=query(Sheet1!A3:X2838,"Select I, H, Q, P, J, N, O, S Where (N>10) and (J>"""&K2&""") Order By Q Desc Limit 80")

Best Answer

Replace

J>"""&K2&"""

J>"&K2&"

The above because QUERY doesn't require a delimiter for numbers

The spreadsheet doesn't include the formula referred in the question.

It contains =query(Sheet1!A3:X2838,B2) where the content of B2 is not a formula.

Replace the content of B2 by

="Select I, H, Q, P, J, N, O, S Where (N>10) and (J>"&K2&") Order By Q Desc Limit 80"

Related Solutions

Google Sheets – Query Syntax Using Cell Reference

Please try syntax of this kind:

=QUERY(Responses!B1:I, "Select B where G contains '"&$B1&"'")

Google Sheets Query – How to Reference a Cell Containing a Date

Indeed, one cannot put things like "A6" inside the query string because then it would just a part of a string, not a cell reference. Instead, concatenate the value of the cell to the string:

=JOIN(", ", UNIQUE(QUERY(A2:F4, "select E where day(A) ="&day(A6))))

You don't need transpose, because join works equally well with one-dimensional arrays of either direction: vertical or horizontal.

That said, I find a solution using filter slightly more readable:

=join(", ", unique(filter(E$2:E$4, A$2:A$4 = A6)))

Best Answer

Related Solutions

Google Sheets – Query Syntax Using Cell Reference

Google Sheets Query – How to Reference a Cell Containing a Date

Related Topic