tl;dr Need a way to substitute 'range_string' in IMPORTRANGE with cell referencing to an input field in the current sheet.

I started off with this query(working well):

=Query({IMPORTRANGE("URL_Y","25 July -  31 July!A16:E26")},"select Col5 where Col3 is not null order by Col5")

I want the formula to apply to new sheetnames in workbook Y. I tried to replace "25 July - 31 July" using cell reference by:

1. Having an input box(A1 in the current worksheet) for my friend to paste the new sheet name created in Y.

2. In cell M3, I used =CONCATENATE(A1,"!A16:E26") so that M3 could be referenced directly as range_string:

   =Query({IMPORTRANGE("URL",=indirect(M3))},"select Col5 where Col3 is not null order by Col5")
   

   but it returned:

   function INDIRECT parameter value is '25 July -  31 July!A16:E26'. It is not a valid cell/range reference.
   

Is it because there are no " " double quotation marks around the string? I tried CHAR(34) but it gave '"25 July - 31 July!A16:E26"', with the same error above.

It's my first time working with these and I'm having trouble allowing Apps Script to run (autoflagged as unsafe) so I'm stuck with formulae, maybe macros. Tried googling for the whole day but I'm not getting anywhere.

Otherwise, I'm open to any suggestions. How can I achieve this? (Removed the actual URL as there are personal identifiers are found in the sheets, sorry about that!)

CodePudding user response：

Instead of the concatenate formula try (in M3)

="'"&A1&"'!A16:A89"

Then in the importrange try

=Query({IMPORTRANGE("URL", M3)},"select Col5 where Col3 is not null order by Col5")