Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query

Autofill Google Sheet rows with a formula

I need to combine multiple cells in Google Sheets and have it autofill as new rows are populated. I tried using the ARRAYFORMULA, however it does not update the new cell reference. It only copy/pastes the first row formula and applies to the rest of the rows. Please see attached image for reference.

So I need to combine columns B, C & D in column A, while the formula in column A should dynamically reference the new row. Here's the formula used:

=ARRAYFORMULA(IF(ISBLANK(B:B),"",IF (B:B <> "",concatenate(text(B1,"yyyy-mm-dd") & C1 & D1 & E1),"COMBO")))

In the formula above, is there a way for values B1, C1, D1 to dynamically update to C2,D2, etc,...?

Thanks!

Solution

  • Just like mentioned in the comments, you can use FILTER and use this formula

    =FILTER(TEXT(B:B,"yyyy-mm-dd") & "" & C:C & "" & D:D & "" & E:E,B:B<>"")

    Furthermore, you can read more about the FILTER function by checking this link: