0

For this sample table:

A B C D E
1 Range!A1:C5 URL1 = Formula here
2 URL2
3 URL3
4 ...

I have this working formula in C1:

={IMPORTRANGE(B1, A1); IMPORTRANGE(B2, A1); IMPORTRANGE(B3, A1)}

A1 contains the range to pull from the target Sheets.

Links to the target Sheets are found in column B.

(This is a simplified example. My actual Sheet has 21 entries in column B.)

Question:

If I will have to add/remove URLs in column B, I have to adjust the formula in C1 and add/remove IMPORTRANGEs.

I would like to have a formula that automatically adjusts to the entries in column B.

Solutions tried:

I tried this, but it did not work:

={JOIN("", ARRAYFORMULA(IF(LEN(B1:B), "IMPORTRANGE(""" & B1:B & """, """ & A1 & """); ", "")))}

The JOIN function returns a text that should be identical to what the array { ... } parses as a formula. But it doesn't. Wrapping it with INDIRECT also does not work.

Any suggestions on how I could make this work?

Majal
  • 1,635
  • 20
  • 31

1 Answers1

0

if by "working formula" you mean valid formula that returns data then only thing you can do is hardcode it like this:

=QUERY({
 IFERROR(IMPORTRANGE(B1, A1), {"","",""}); 
 IFERROR(IMPORTRANGE(B2, A1), {"","",""}); 
 IFERROR(IMPORTRANGE(B3, A1), {"","",""})}, 
 "where Col1 is not null", )

A1:C5 = 3 columns = {"","",""}

you can generate formula dynamically with arrayformula but the result will be always a text string and not active formula. tho if you wanna go this way you will need a script that will convert your text string into active formula. example:

player0
  • 124,011
  • 12
  • 67
  • 124