how to reference data of query in a specific cell

Question

This is what I am using, and it works fine, but whenever I add a sheet, I add it to the formula below as well.

=QUERY({Sheet1!A:C;Sheet2!A:C;Sheet3!A:C},"select Col1,Col3,Col4 where Col1 is not null",0)

I would like to have this {Sheet1!A:C;Sheet2!A:C;Sheet3!A:C} in a Cell, let's say D1 then my formula goes like this =QUERY(D1,"select Col1,Col3,Col4 where Col1 is not null",0).

This is just to demonstrate, and I know it will consider D1 as the data itself

so I tried:

indirect("D1")

t(D1)

text()

textjoin()

join()

it always treats D1 as the data and ignores the text in it.

Answer 1

arrays of ranges are not supported under INDIRECT but you can generate your formula with another formula (and then just copy-paste it or use script to auto-update a given cell with the generated formula):

={""; ARRAYFORMULA("=QUERY({"&JOIN("; ", 
 IF(B4, "IFERROR(", )&B1&SEQUENCE(B3)&"!"&B2&
 IF(B4, ", {"&JOIN(",", REGEXREPLACE(""&SEQUENCE(
 MATCH(REGEXEXTRACT(B2, "\:(.)"), CHAR(ROW(64:94)), )-(
 MATCH(REGEXEXTRACT(B2, "(^.)"),  CHAR(ROW(64:98)), )-1)), 
 "\d+", """"""))&"})", ))&
 "}, ""select Col1,Col3,Col4 where Col1 is not null"", 0)")}

demo spreadsheet