google-sheetsconcatenationimportrange

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

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?

Solution

  • 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