I’m working on a Google Sheet that gets data from a form. I’ve got this fancy formula to format my data:
=IF(ISBLANK(A1:B1), C1, IF(ISBLANK(B1), C1 & " | " & A1, C1 & " | " & A1 & " | " & B1))
It works great, but I need it to automatically apply to new rows when they’re added. I tried wrapping it in ARRAYFORMULA, but got a warning about overwriting data. Any ideas on how to make this work without messing up my other columns? Thanks!
I’ve encountered a similar issue before. Instead of using ARRAYFORMULA, which can be problematic with complex formulas, consider using Google Sheets’ built-in feature to automatically extend formulas when new rows are added.
Apply your formula to the initial row and then double-click the blue square at the bottom-right of the cell. This action propagates the formula to subsequent rows, including any new ones. This method is effective when adjacent columns contain data, but it’s wise to review and reapply the formula when making significant changes to the sheet.
yo, i got a quick fix for ya. use the LAMBDA function. it’s like a mini-program inside ur sheet. something like this:
=LAMBDA(a,b,c,IF(ISBLANK(a:b),c,IF(ISBLANK(b),c&“|”&a,c&“|”&a&“|”&b)))(A2:A,B2:B,C2:C)
this’ll apply to new rows automatically. no need for arrayformula or anything fancy. give it a shot!
I’ve grappled with this exact problem in my work projects. Here’s a trick that’s saved me countless hours: use the QUERY function. It’s a game-changer for handling dynamic data.
Try something like this in the first cell of your output column:
=QUERY(A1:C, “SELECT C, A, B WHERE A IS NOT NULL OR B IS NOT NULL OR C IS NOT NULL”, 0)
This formula will automatically adjust as new rows are added, and it respects your original logic. It selects all non-empty rows and concatenates the columns with a pipe separator.
Just make sure to give it enough room to expand. I usually clear out a good chunk of the column below it. It’s not perfect, but it’s been reliable for me across various projects.