2

I have a script that pastes a formula from one cell into a range in a column. When pasted the range just disappears. I mean the whole formula is OK except the pointer. Any ideas how to deal with this?

function formulas() {
  var planil = SpreadsheetApp.getActiveSpreadsheet();
  var shimi = planil.getSheets()[0];
  shimi.getRange("E5").copyTo(shimi.getRange("B3:B100"));
}

Formula to copy:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE((A:A),CHAR(34)&"novedadesmusi@daeva.com.ar"&CHAR(34)&" <novedadesmusi@daeva.com.ar","MUSIMUNDO"),CHAR(34)&"novedadesdtv@daeva.com.ar"&CHAR(34)&" <novedadesdtv@daeva.com.ar","DIRECTV"),CHAR(34)&"novedadestlc@daeva.com.ar"&CHAR(34)&" <novedadestlc@daeva.com.ar","TLC"),CHAR(34)&"novedadessony@daeva.com.ar"&CHAR(34)&" <novedadessony@daeva.com.ar","SONY")

Formula Copied :

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(({}),CHAR(34)&"novedadesmusi@daeva.com.ar"&CHAR(34)&" <novedadesmusi@daeva.com.ar","MUSIMUNDO"),CHAR(34)&"novedadesdtv@daeva.com.ar"&CHAR(34)&" <novedadesdtv@daeva.com.ar","DIRECTV"),CHAR(34)&"novedadestlc@daeva.com.ar"&CHAR(34)&" <novedadestlc@daeva.com.ar","TLC"),CHAR(34)&"novedadessony@daeva.com.ar"&CHAR(34)&" <novedadessony@daeva.com.ar","SONY")
  • 1
    omg , believe me i tried this 1 hour , but NOW is workingg !!! loooll thank you very muchhh :D – user67277 Apr 29 '14 at 1:40
2

It seems that when copying from ColumnE into ColumnB without the $s the pasted formulae were trying to automatically adjust but since A:A is the first column they were unable to adjust to a column further to the left, and that the solution is merely to fix the column reference in the source formula.

  • Exactly that was the issue thank you very much pnuts :D ! – user67277 Apr 29 '14 at 1:54

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.