google-sheets

Convert a string into a valid data range

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)
Solution

  • 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.