I'm trying to dynamically create a date range in a query function in Google Sheets. It works fine when I enter the range directly, but when I enter it as a string formed from concatenate it doesn't work.
How can I convert the concatenated value into a valid data range. The formula I have is below:
=query(CONCATENATE(S$1,"!A:BK"), CONCATENATE("SELECT I, AG, Y, Z WHERE C = '",$A4, "'"),0)
Passing a range reference is not the same as passing a string that looks like a range reference.
If you are building your range like this you want to convert the range reference like string into an actual range reference using INDIRECT()
like this:
=query(indirect(S$1&"!A:BK"), CONCATENATE("SELECT I, AG, Y, Z WHERE C = '",$A4, "'"),0)
Note that I removed the concatenate and replaced it with an ampersand. This is not necessary, it's just syntactic sugar.