Use dynamic created Query text as a Query formula in Google Sheets

Question

First of all this is my first question here...

Ok, and now my problem: I created a formula that generates a dynamic Query if you are searching for data in multiple sheets. I just write the names of the sheets in green field and it changes the Query. The text for the Query is correct, but now I want this to by the actual formula in a cell to display the data.

If I use the = and add the cell with the Query text, it copy the text. I tried the INDIRECT formula, but it does the same. How can I use a this dynamic text to be my Query to dispaly the data depending on the amount of names in the green field?

EDIT: As advised by user doubleunary (as mentioned, this is my first time), I'm going to rephrase the problem. In an spreadsheet a manager imputs for every task the amount a worker has made. He creates an new sheet for each date and imputs the data for that day. Each day can have different rows of data. The boss wants a spreadsheet that display in one sheet all that has been done in the current month. I used Query because you can easy join data from multiple sheets and ignor the empty rows. But the problem is, that every day a new sheet is added with a new "name" (date). And the easyest way I found tho make a dynamic Query, without the Boss to manualy edit the Query, was to create a text with TEXTJOIN formula, in witch he ony needs to enter the new "name" (date). If there is a better was, please share, but it should not be in App Script if possible.

Thank you.

Welcome to [Stack Overflow](https://stackoverflow.com/tour). What you are asking can be done, but the way you envision doing it is probably not the best way. See [What is the XY problem](https://meta.stackexchange.com/a/233676) and [edit](https://stackoverflow.com/posts/74392306/edit) your question to ask the X. — doubleunary, Nov 10 '22 at 16:45
share a copy / sample of your sheet with an example of the desired output — player0, Nov 10 '22 at 19:00

score 0 · Answer 1 · answered Nov 10 '22 at 17:03

Easily you can't transform a text into a formula.

In this case, you should create the query formula and insert the ranges with Indirect function, like this:

=Query({Importrange(LINK,Indirect("'"&O4&"'!I38:S107"));Importrange(LINK,Indirect("'"&O5&"'!I38:S107"))},"Select * Where Col1 is not null",0)

score 0 · Answer 2 · answered Nov 10 '22 at 17:30

If the formula you quote and the data sheets are all in the same spreadsheet file, you should not to use importrange() but indirect(), like this:

=query( 
  { 
    indirect(O4 & "!I38:S107"); 
    indirect(O5 & "!I38:S107") 
  }, 
  "where Col1 is not null", 
  0 
)

In the event the row references such as I38:S107 change from time to time as well, you can put those references in a cell and refer to them the same way.

score 0 · Answer 3 · answered Nov 10 '22 at 19:09

try (assuming url is same for all sheets):

=QUERY(LAMBDA(x, QUERY(REDUCE(SEQUENCE(1, 11), x, 
 LAMBDA(a, c, {a; IMPORTRANGE("url", c"!I38:S107")})), 
 "where Col1 is not null", 1))
 (O4:INDEX(O:O, MAX((O:O<>"")*ROW(O:O)))), "offset 1", )

more examples: https://stackoverflow.com/a/74280310/5632629

Use dynamic created Query text as a Query formula in Google Sheets

3 Answers3