Search code examples
arraysgoogle-sheetsgoogle-sheets-formulagoogle-query-languagetextjoin

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: =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"", )")}

    enter image description here

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