Autofill Google Sheet rows with a formula

2
votes

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!

2
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
1. you use array formula but you just operate B1, C1, D1 & E1 - user11982798
2. You can change B1 to B:B, C1 to C:C, D1 to D:D and E1 to E:E - user11982798
3. You can simply use filter instead of Arrayformula - user11982798
4. what the necessary of concatenate above? - user11982798
5. try this one =filter(text(B:B,"yyyy-mm-dd") & " " & C:C & " " & D:D & " " & E:E,B:B<>"") - user11982798

2 Answers

0
votes

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:

0
votes

previous answer can be simplified to just:

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

otherwise, if you want to use your formula:

=ARRAYFORMULA(IF(B1:B="",,TEXT(B1:B, "yyyy-mm-dd")&C1&D1&E1, "COMBO")))

or:

=ARRAYFORMULA(IF(B1:B="",,TEXT(B1, "yyyy-mm-dd")&C1:C&D1:D&E1:E, "COMBO")))

or:

=ARRAYFORMULA(IF(B1:B="",,TEXT(B1:B, "yyyy-mm-dd")&C1:C&D1:D&E1:E, "COMBO")))

or:

=ARRAYFORMULA(IF(B1:B="",,TEXT(B1, "yyyy-mm-dd")&C1&D1&E1, "COMBO")))