Expandable Importrange

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:
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:

 IF(C4:C="",,"IMPORTRANGE("""&C4:C&""", ""Sheet1!C5:F"")"))&
 "}, ""where Col1 is not null"", )")}

enter image description here

so it will automatically create a formula for you and then you just copy-paste it where you need it

Leave a Reply