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".

  • Why don't you share an example doc with us, so we can have a look? – Jacob Jan Tuinstra Mar 13 '15 at 21:47
  • Jacob, I could share an example but there's really nothing to it. IMagine you have a number 100 in A1, number 150 in B1 and in C1 a formula =A1+B1. Now, instead of dragging it down the entire C column I wish to make a formula/script that would copy this formula from C1 into every sell in the C column. Also, I'd like the formula to copy to all the rows (column C) that might be inserted at the bottom of the document. Thanks – Alexander Starbuck Mar 14 '15 at 22:56

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


          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=","") & 
      , " ",  "%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")))


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.


enter image description here


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

  • 1
    AWE-SOME! It worked like a charm. I thought I'd have to do this by scripting and I'm just starting with it. IF I decide to do this, should I create custom menu item which with learning it. – Alexander Starbuck Mar 16 '15 at 14:59
  • You can create a custom formula to perform the same action...but with less text to type in. – Jacob Jan Tuinstra Mar 16 '15 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.