Google Sheets Query - Changing Query Data Range Based using a reference cell

0
votes

I have seen the use of a reference cell to take the query place of the function.

For example:

Instead of A4 containing the entire function: =QUERY('Staff List'!A2:E12, "SELECT A, B, C, E WHERE E = 'No'")

The query is referenced in another cell like this. =QUERY('Staff List'!A2:E12,A1)

where A1 contains "SELECT A, B, C, E WHERE E = 'No'" so instead of Editing A4 you can just edit A1 when needing to change the query.

My Question is this: Can the same concept be applied to the date I wish to reference in a query?

For example can use something like this

=SORT(Query(A1,"Select * where Col7 >= DATE """&TEXT(A2,"yyyy-MM-dd")&""" AND Col7 <= DATE """&TEXT(B2,"yyyy-MM-dd")&""""),1,true)

where I insert {'1'!P5:V1000;'2'!P5:V1000;'3'!P5:V1000} into cell A1

Instead of this:

=SORT(Query({'1'!P5:V1000;'2'!P5:V1000;'3'!P5:V1000},"Select * where Col7 >= DATE """&TEXT(A2,"yyyy-MM-dd")&""" AND Col7 <= DATE """&TEXT(B2,"yyyy-MM-dd")&""""),1,true)

So far i had tried this to no avail

=SORT(Query(""A1"","Select * where Col7 >= DATE """&TEXT(A2,"yyyy-MM-dd")&""" AND Col7 <= DATE """&TEXT(B2,"yyyy-MM-dd")&""""),1,true)

1
google-sheets
share a copy of your sheet – player0

1 Answers

1
votes
=QUERY(1st param, 2nd param, 3rd param)

1st param of query can be referenced only as non-arrayed range like:

=QUERY(INDIRECT(A1))
 ____
 where A1 contains range A1:C

arrayed ranges like these are not allowed:

{A1:C; D1:F}
 ____
{INDIRECT(A1:C); INDIRECT(D1:F)}

as for the 2nd and 3rd QUERY param, those are allowed to be referenced as a string even if it contains a date atribute