Google Sheets query where other sheet equals

0
votes

I have a workbook with two sheets. I want to query a sheet called Farming that has rows of numbers associated with different objects which I want to sum over.

I want to use query so that the two different sheets can be sorted and filtered without breaking.

I have got this far:

=query(Farming!A2:Z1000, 'select F+G+H+I+J+K+L where "Farming"!B="B7"', 0)

Where the column B on each sheet is the unique reference number for the objects. However, I'm getting a syntax error and I'm not sure where to go from here.

Thanks for your help!

2
google-sheetsgoogle-query-language

2 Answers

3
votes

It's unclear to me whether you're getting B7 from the Farming page or the sheet the formula is on. If it's the former, the first function works. If it's the latter, the second works. These examples are based on text rather than numbers being the value in cell B7. If B7 is a number you don't have to do the quotes.

=query(Farming!A2:Z1000, "select F, G, H, I, J, K, L where B="&"'"&Farming!B7&"'", 0)

=query(Farming!A2:Z1000, "select F, G, H, I, J, K, L where B="&"'"&B7&"'", 0)

Example with the value in B1.

3
votes

Try

=query(Farming!A2:Z1000, "select F+G+H+I+J+K+L where B='"&B7&"'", 0)

Remarks

The select statement in query

  1. should be quote (") enclosed rather than single quote/apostrophe (') enclosed.

  2. it could reference fields from the data argument by using column names (A, B, C, ) or aliases like Col1, Col2, etc. when the data isn't a reference.

  3. String values inside the SQL statement should be single quote/apostrophe enclosed