Search code examples
google-sheetsgoogle-sheets-querygoogle-sheets-formula

How to importrange dynamically based on dropdown/variable

I'm trying to dynamically retrieve all rows where a status = [Dynamically chosen value] from a drop down list.

below you can see my current formula:

=query(IMPORTRANGE("https://example.com", "Tracker!A1:V"), "select * where Col7 = 'B1' ")

enter image description here

Here is an example where you can see what I'm trying to do (Status is A1, and A2 is the formula grabbing all rows from another sheet) enter image description here

If I select the "A1" in the formula is brings up "In Progress" so it appears to be referencing the correct value, but it doesnt work unless I explicitly write the status into the formula as a text object..

As noted below: enter image description here

Solution

  • to use cell references, you have to escape them using '"&cell&"'

    QUERY(IMPORTRANGE("your_url","your_sheet!your_range"),"Select * where Col7 = '"&A1&"'",0))