0

Is there a formula that will allow me to use a range of sheet ids to stack import ranges from multiple sheets?

Example

=importrange(A2:A,"Sheet1!A:F") in the example is meant to be illustrative of what I'm trying to achieve.

I'm aware I can use

={IMPORTRANGE("id1","Sheet1!A:F");IMPORTRANGE("id2","Sheet1!A:F"); IMPORTRANGE("id3","Sheet1!A:F")}

however, what I'm hoping to achieve is a growing list of ids in column A that when a new id is entered (and after creating a method for allowing permissions) will add on and expand the stack of importranges.

The closest I got was this but I'm getting a space on either side of the id and wasn't able to progress from there.

wsup55
  • 1
  • Take a look at that [answer](https://stackoverflow.com/a/73099704/19529694) for a similar quiestion to your's. – Osm Jul 26 '22 at 06:15
  • The short answer is there is no similar function to Excel's EVALUATE() function in Google Sheets to evaluate text in a cell to a formula, take a look at this [answer](https://stackoverflow.com/a/73099704/19529694) to make formula concatination easier – Osm Jul 26 '22 at 06:37
  • @Osm *"One **cannot use** the `IMPORTRANGE` function the way you wish (concatenate or join). But. **[You have an alternative](https://stackoverflow.com/a/73113528/1527780)**."* – marikamitsos Jul 26 '22 at 06:49
  • @marikamitsos The Point is to set it once and forget it, he said "what I'm hoping to achieve... expand the stack of importranges.", and i wish there is a solve for this. – Osm Jul 26 '22 at 07:07
  • @Osm You **do** *"set it once and forget it"*. You set up the the basic part, multiple times, **once**. Give it a try. – marikamitsos Jul 26 '22 at 07:15
  • @marikamitsos I tested it before and double checked now, Expand on the answer and share the sheet with him, I mean like a hard coded formula with about 200 to 300 line to fill his sheets links. like this it will be the closest to set and forget workaround. – Osm Jul 26 '22 at 08:02
  • This is interesting, I hadn't considered this. Was hoping to find something that would work directly with formulas but this is a reasonable fallback to provide to my users that would be reasonably clear to follow when updating. Thanks for this. – wsup55 Jul 26 '22 at 20:12

1 Answers1

0

not possible to accomplish directly. the best you can do is to use a formula to generate formula:

=INDEX({""; "={"&TEXTJOIN("; ", 1, "IMPORTRANGE("""&
 FILTER(A2:A, A2:A<>"")&""", ""Sheet1!A:F"")")&"}"})

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    this works great for generating the formula, much cleaner than the path I was on. Thanks! – wsup55 Jul 26 '22 at 20:13