Google spreadsheets using ArrayFormula() to copy a formula into each row in the column as information is added and use data in corresponding row

0
votes

I'm trying to copy a formula into each row of a column as the google sheet is attached to a google form that will keep importing data, and I only want the formula in each cell to use information from the cells in different columns in the same row however all the resources online that I've seen seem to only use Arrayformula to take all of the information added to one column and use it to calculate something in each cell, I only want information from the same row. Here is the formula I want to add to each cell in the column.

=IF(ISBLANK(G2), "",CONCATENATE(G2,"-",H2,"(",I2,")", " to ",G2,"-",H2,"(",J2,"), ",K2,", ",L2,", ",M2,", ",N2))

So for instance, if I was in cell C3 I would want to formula to reference G3, H3, I3, J3, K3, L3, M3 and N3 and not just concatenate all the values in all of G, H, I, J, K, L, M, and N like I experienced when trying to implement ARRAYFORMULA myself.

Here is what I had previously that didn't work:

=ARRAYFORMULA(IF(ISBLANK(G2:G), "", CONCATENATE(G2:G, "-",H2:H,"(",I2:I,")", " to ",G2:G,"-",H2:H,"(",J2:J,"), ",K2:K,", ",L2:L,", ",M2:M,", ",N2:N)))
1
arraysgoogle-sheetsconcatenationarray-formulas
Can you share a public sheet with the data you receive and an example on how do you want the result? – Kessy

1 Answers

2
votes

The concatenate function doesn't work when using arrayformula. You can however use the ampersand (&) to concatenate cells, so something like this:

=ARRAYFORMULA(IF(G2:G="","",G2:G & "-" & H2:H & "(" & I2:I & ")" & " to " & G2:G & "-" & H2:H & "(" & J2:J & "), " & K2:K & ", " & L2:L & ", " & M2:M & ", " & N2:N))