How do you reference a cell in the
clause of a Google Sheets
To reference a cell in the
, simply break the
string by closing with a double-quoted string
append the concatenation symbol
then reference the cell append the
to open up the
so you can continue writing the rest of your
Do be mindful it does depend on the type of value being referenced in the cell as well. If the cell reference is a string, you will need to make sure you wrap the cell reference in single quotes .
For example, suppose I had the following
If I wanted to create a
function on this data set and I wanted to filter the rows where column
is the currency
, using the
function with a
clause would look like this:
=QUERY(A:B,"SELECT * WHERE A = 'USD'")
were placed on a separate
sheet (which would be highly encouraged when interacting with raw data), the result would look like this:
WHEREclause filters the rows according to the condition.
Clause Referencing Cell
So the current example is good if you just wanted a report on the
transactions in your data set, but what if the requirements change and you need to report on
or on other currency transactions?
You could create another sheet and label this
in this sheet being the currency to report transactions on. Therefore, in the
sheet you can modify the
function to reference this cell instead of hardwiring the required currency into the formula.
Here’s how your
sheet would look:
WHEREclause that references a cell.
As you can see from the above result, the output is still the same – and it should be.
contains the value
. But notice in the new formula that
changed to be the actual reference
– this is all that is needed to reference a cell’s value within your
A common mistake when creating your formula using the
function when referencing external cell values could be something like this:
Unable to parse query string for Function
If you get this error, don’t fret it’s a sign you’ve likely forgotten to wrap your string reference in single quotes. Just remember your
statement is opened with double quotes, and when you are referencing a value that is to be assessed as a string, you need to open a single quote before closing the double quotes and inserting your cell reference.
How To Reference A String In
statement when referencing a string should open with a single quote…
"SELECT * WHERE A = '
… before closing the double quotes…
"SELECT * WHERE A = '"
… append your cell reference (remembering to add the concatenation symbol at the end)…
"SELECT * WHERE A = '"&Settings!A1&
… open with double quotes again…
"SELECT * WHERE A = '"&Settings!A1&"
… and close the single-quoted string…
"SELECT * WHERE A = '"&Settings!A1&"'
… and you can continue with the rest of your
string. Otherwise, if you’ve finished close with a double-quoted string…
"SELECT * WHERE A = '"&Settings!A1&"'"
If it helps write the actual string the cell value contains first before swapping it out with the cell reference.
clause can enable you to filter your data sheet by only working on a subset of that data. If your requirements on filtering are likely to change your
statement in the
function can reference cell values.
To reference cell values in a
function insert a cell reference into the string just as you normally would with any string in a spreadsheet: insert double quotes to close the current
string, append the concatenation symbol
, insert the cell reference (i.e.
), append another concatenation symbol
, and then open the double quotes again to continue the