CODEWITHSUNDEEP
google-sheets
Keith D
Keith D

Reputation: 413

Query formula in Google Spreadsheets

I am trying to make a dynamic link inside query formula using Google SpreadSheet. 

=QUERY(Test!$A$1:$M$1000, "select G where (L='Leader' and M='Team 06')")

The Above code will work but since I have lots of teams I want to be able to copy a formula that looks something like this:

=QUERY(Test!$A$1:$M$1000, "select G where (L='Leader' and M=$A6)")

where $A6 contains the text "Team 06".

I imagine its not working because the $A6 is inside quotes. Any suggestions on a work around. Thanks.

Upvotes: 2

Views: 161

Answers (2)

JPV
JPV

Reputation: 27242

Try:

=QUERY(Test!$A$1:$M$1000, "select G where L='Leader' and M='"&A6&"' ")

Upvotes: 0

Alexander Seeto
Alexander Seeto

Reputation: 36

I modified your query somewhat, but to set M = A6, try wrapping it in ampersands (&) as well as quotes (") to add it to the query string.

Here's an example of something similar to what I think you're looking for:

=QUERY(Test!$A$1:$D$5, "select A where (B='Leader' and (C="&D1&"))")

   A          B       C      D
Option 1    Leader  TRUE    TRUE
Option 2    Member  FALSE   
Option 3    Leader  FALSE   
Option 4    Member  TRUE    
Option 5    Leader  TRUE    

Query Result(s)         
Option 1            
Option 5

Reference: https://support.google.com/docs/answer/3093343?hl=en

Upvotes: 0

Related Questions