Google Sheets – Apply Complicated Formula to Entire Column

google sheets

I tried searching for this but to no avail – how can I copy a quite complicated formula through the whole column AND make it apply to rows which might be added to the bottom of the sheet?

The formula is this:

    =CONCATENATE(A5,IF(ISERROR(FIND("?",A5,1)),CONCATENATE("?"),CONCATENATE("&")),"utm_campaign=",SUBSTITUTE(B5," ", "%20") ,"&utm_medium=",SUBSTITUTE(C5," ", "%20"),"&utm_source=",SUBSTITUTE(D5," ", "%20"),IF(E5<>"",CONCATENATE("&utm_content=",SUBSTITUTE(E5," ", "%20")),),IF(F5<>"",CONCATENATE("&utm_term=",SUBSTITUTE(F5," ", "%20")),))

Basically, what it does is it concatenates data from columns A, B, C, D, E, F with some Google "utm" tags and creates tagged destination link. Along the way it checks for various errors and replaces " " with "%20".

Best Answer

I was able to reduce the formula and add ARRAYFORMULAE to make it work with ranges.

Formula

 =ARRAYFORMULA(
    IF(ISBLANK(A:A), 
      "", 
      SUBSTITUTE(
        ARRAYFORMULA(
          A:A & 
          IF(ISERROR(FIND("?", A:A, 1)), "?", "&") & 
          "utm_campaign=" & 
          B:B & 
          "&utm_medium=" & 
          C:C & 
          "&utm_source=" & 
          D:D & 
          IF(E:E<>"","&utm_content=","") & 
          E:E & 
          IF(F:F<>"","&utm_term=","") & 
          F:F
        )
      , " ",  "%20"
      )
    )
  )

 copy / paste
 =ARRAYFORMULA(IF(ISBLANK(A:A), "", SUBSTITUTE(ARRAYFORMULA(A:A & IF(ISERROR(FIND("?", A:A, 1)), "?", "&") & "utm_campaign=" & B:B & "&utm_medium=" & C:C & "&utm_source=" & D:D & IF(E:E<>"","&utm_content=","") & E:E & IF(F:F<>"","&utm_term=","") & F:F), " ",  "%20")))

Explained

It is not possible to use CONCATENATE formulae in combination with ARRAYFORMULAE. Therefore we need to go about this differently, by using an AMPERSAND to join different strings together. If the concatenated string is prepared and the ranges are chosen, then substitute all empty spaces with %20. This, together with an IF statement to check column A for presence, is all wrapped inside yet another ARRAYFORMULA.

Screenshot

enter image description here

Example

I've created an example file for you: Apply complicated formula to whole column

Related Solutions

Google-sheets – Apply Formula to Newly Inserted Row

The solution below involves a very elegant and generally useful technique popularized by ahab, a Google Docs expert; I suppose he invented it. It uses matrix multiplication.

Move the label "Starting" to the last cell of the Description column, column D.

Move the starting balance to the last cell of the Amount column, column E.

Insert a new blank row 2. This row will be reserved and should be left blank except for the following formula, so you might want to freeze it along with row 1.

Put the following formula in cell F2.

= ArrayFormula( if( isnumber(E2:E)
      ;
    mmult(
      transpose(E2:E) * (row(E2:E) <= transpose( row(E2:E) ) )
        ;
      sign( row(E2:E) )
    )
      ;
) )

Any row having a transaction amount in column E now has a corresponding running balance in column F. The running balance has the desired property that the initial balance is in the last row of column F and subsequent balances read up column F.

If, instead, you want your starting balance in the first (data) row of column F (cell F2) and subsequent balances to read down the column, change <= to >=.

Google Sheets – How to Apply Formula to Entire Column and Auto-Add to New Rows

Use the following formula for that:

=ARRAYFORMULA(ROW(A:A))

I strongly advise you to use hard coded Id's. If for some reason the sheet gets re-arranged, then the Id's will follow with that re-arrangement. An ARRAYFORMULA will stick to its plan and present the range as ever.