I am combining several spreadsheets with identical layouts into one master, and want to create a way to have my query({importrange}) be dynamic, as I will be adding / removing some sheets as time goes on. I have all of my sheet addresses in column C, so my formula right now looks like:
=QUERY({Importrange(C4,Sheet1!C5:F);Importrange(C5,Sheet1!C5:F);…}
This works fine, but any time I add/remove a sheet I would have to edit a very long string.
Is there a way for QUERY or IMPORTRANGE to reference another cell that combines my various spreadsheets listed in column C?
I’ve tried variations of CONCATENATE, JOIN, etc to combine C into one cell that is referenced in the QUERY OR IMPORTRANGE, but no luck so far.
Trying to future-proof a little…
>Solution :
no, but you can do this:
={""; ARRAYFORMULA("=QUERY({"&TEXTJOIN("; ", 1,
IF(C4:C="",,"IMPORTRANGE("""&C4:C&""", ""Sheet1!C5:F"")"))&
"}, ""where Col1 is not null"", )")}
so it will automatically create a formula for you and then you just copy-paste it where you need it