I have a query in a Google Sheet structured like so:

=QUERY({IMPORTRANGE(Expenses_2018Q4,"DATA_Expenses"); IMPORTRANGE(Expenses_2019Q1,"DATA_Expenses");IMPORTRANGE(Expenses_2019Q2,"DATA_Expenses")},"SELECT * WHERE Col17='"&ParamProjectKey&"' ORDER BY Col3, Col4 ASC")

This query uses three data arrays using IMPORTRANGE. Each Range comes from an external sheet, and each sheet has a named range called "DATA_Expenses".

The formula works nicely, and I get the results displayed as expected and desired.

What I would like to do is change this section:

{IMPORTRANGE(Expenses_2018Q4,"DATA_Expenses"); IMPORTRANGE(Expenses_2019Q1,"DATA_Expenses");IMPORTRANGE(Expenses_2019Q2,"DATA_Expenses")}

I would like it to reference a named range in the current sheet, so that the formula now looks like:

=QUERY(QUERY_DATA_RANGE,"SELECT * WHERE Col17='"&ParamProjectKey&"' ORDER BY Col3, Col4 ASC")

I have been able to create a named cell reference that includes the IMPORTRANGE values exactly as per the original formula, but I can't figure out how to get the QUERY to evaluate it. It seems that the content of QUERY_DATA_RANGE is being passed in as a string, rather than as an evaluated array (if that makes sense). I tried using ArrayFormula to solve this - but with no luck so far.

  • can you share a copy of your sheet ?? – user0 Apr 24 at 12:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.