2

I would like to reference (pull in) formulas (not the result of the formula) from one sheet into multiple sheets so that they calculate on the sheet they are referenced.

For example, if I have =SUM(Q2:X2) in a formulas sheet, I would like to pull that into 10 other sheets (budget A sheet, budget B sheet, etc.) so that it would sum Q2:X2 for each budget sheet it is referenced into, not the formulas sheet.

I have many budget sheets with the same formulas in the same locations. However, they can change from quarter to quarter. I would like to update the formulas in one place, rather than in each sheet. I think this might be possible with getFormula, but I'm very new to scripting and not sure how I would use that.

  • 1
    Thank you to coming back and edit your question. Please add a brief description of your search/research efforts as is suggested on How to Ask (we already have several questions that uses setFormula) – Rubén Oct 8 at 22:09
  • This can't be done without scripts unless you break down the formula and then use INDIRECT – Oren Pinsky Oct 9 at 18:18
0

You want to copy a formula from one sheet to many other sheets in the same spreadsheet.

You are right that a script including getFormula (for which there is relatively little discussion) and setFormula (for which, as @Rubén says, there are many precedents) is on the right track. The following code provides a working example of how you might proceed.

  • Cell B3 of "template" contains a formula =sum(C3:G3). This formula does not exist on any other sheet (or a different formula may exist in that cell).
  • the formula is obtained using getFormula doc
  • the names of the other sheets are declared in the variable budgetsheets
  • a loop is created work through each of the sheets
  • in each sheet

The code could be more efficient (such as selecting the cell to be copied, making the range a variable, selecting the sheet names programmatically) but it is adequate as a working example.


function wa13533201() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourcename = "template";
  var source = ss.getSheetByName(sourcename);
  var sourcerange = source.getRange("B3");
  var sourceformula = sourcerange.getFormula();

  var budgetsheets = [
    "Sheet2",
    "Sheet3",
    "Sheet4"
  ];

  for (var i=0;i<budgetsheets.length;i++){

    //Logger.log("DEBUG: sheet = "+budgetsheets[i]);
    var targetname = budgetsheets[i];
    var target = ss.getSheetByName(targetname);
    var targetrange = target.getRange("B3");
    targetrange.setFormula(sourceformula);
  }
}

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.