How can I make cell reference dynamic in IMPORTRANGE in Google Sheets?

0
votes

I am using a formula to import some values from a google sheet and concatenating them to make one value.

=CONCATENATE(IMPORTRANGE("url", "'Sheet1'!A2")," ",IMPORTRANGE("url", "'Sheet1'!B2")," ",IMPORTRANGE("url", "'Sheet1'!C2")," ",IMPORTRANGE("url", "'Sheet1'!D2"))

How can I make the cell references dynamic so that if I drag it down cell value changes to A3,B3,C3,D3?

1
google-sheetsconcatenationimportrange
I would strongly suggest to do a single importrange on a separate tab with the WHOLE range as the second parameter ("'Sheet1'!A2:D"). Then do a concatenation per row from that table. - JPV

1 Answers

1
votes

The IMPORTRANGE formula is not a dynamic one and therefore does not allow modifying the ranges to it by dragging it down, as with other formulas.

However, a way to solve your issue is to do the IMPORTRANGE on another sheet, like this:

=IMPORTRANGE("URL", "'Sheet1'!A1:D4")

Assuming that the data was imported into a Import sheet:

imported data

You can use the below formula on another sheet and drag it down:

=ARRAYFORMULA(JOIN(" ",FILTER(Import!$A1:$D1, Import!A1:D1<>"")))

After the above steps, this is how the end result will look like:

data after concatenation

Note

Also, please bear in mind that the above formulas were used considering the imported data was in the A1:D4 range. You might need to adjust that to your case.

Reference