Sheets Formula Template

Question

Is there a way to setup a cell that contains a formula, and have other cells copy that formula and apply it locally?

Please see sample sheet.

I setup formulas in cells B6:B11, which reference cells E6:E11. I want cells B16:B21 to use the same formulas as cells B6:B11, and reference E16:E21. When I change the formulas in B6:B11, I want it to auto-apply to the rest of the dependent cells. I've tried doing this through INDIRECT with no luck, and I can't apply ARRAYFORMULAs to everything I need to do. I also tried setting one cell that uses MOD(ROW()) to check the row number and apply a formula. That was a VERY long formula, and always referenced the parent rows, not the child rows.

Thanks in advance!

https://docs.google.com/spreadsheets/d/1SXeTv25Vh6as9bSTNW7B-GcdnIyaf0YAVClRKPkjsm8/edit?usp=sharing

Would a solution based on [Google Apps Script](https://developers.google.com/apps-script) be valid for you? — Emel, Mar 03 '22 at 09:56

score 0 · Answer 1 · answered Mar 03 '22 at 11:04

0

possible with script. example:

function onEdit() { 
var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');  // sheet name
var src = sheet.getRange("G2");                                   // cell which holds the formula
var str = src.getValue();  
var cell = sheet.getRange("G10");                                 // cell where I want the results
cell.setFormula(str);              
}

application example: https://stackoverflow.com/a/61819704/5632629

answered Mar 03 '22 at 11:04

player0

124,011
12
67
124

Is it possible to set an auto-repeat in that script based on row number? If I need the formula in A1 to repeat every 10 rows, so that it shows up in A1, A11, A21, etc. That would be the icing on the cake. – user15250594 Mar 03 '22 at 14:11
Tried this and it copies the value in the parent cell to the child cell, not the formula. I'm not great with app scripts but will keep toying with it. – user15250594 Mar 03 '22 at 14:18

score 0 · Accepted Answer · answered Mar 10 '22 at 09:00

You have an interesting problem on your hands, and I think its solution is easier than it seems. I have written a small script that you can use as a guide, I believe that without having experience you will be able to complete it.

const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1')

const setDynamicFormulas = () => {
  /* Create the dinamic formulas */
  const formula1 = (n) => `=IFS(B${n}<=10,40,B${n}>10,50)`
  const formula2 = (n1, n2) => `=SUM(E${n1}:E${n2})`
  /* We loop over the sheet making 10 cell jumps */
  for (let i = 0; i < 100; i = i + 10) {
    /* Assing the values for our formulas */
    const f1 = formula1(7 + i)
    const f2 = formula2(5 + i, 14 + i)
    /* Set the values for the formulas */
    ss.getRange(6+i, 2).setFormula(f1)
    ss.getRange(7+i, 2).setFormula(f2)
  }
}

What I do in the script is to create a dynamic formula for each of the formulas you want to iterate. For example for the formula that is in B6:=IFS(B7<=10,40,B7>10,50), we realize that the dynamic part, is the one that refers to B7 in the following iterations will be: B17, B27, etc... So, we already know that the number increases in ten units each iteration. How can we write a dynamic formula with this information? Very easy, we simply indicate between ${} our dynamic part. It would look like this:

const formula1 = (n) => `=IFS(B${n}<=10,40,B${n}>10,50)`
// formula1(17) will be  =IFS(B17<=10,40,B17>10,50)

Okey, but what happens with the next formula? =SUM(E5:E14) we have two dynamic parts, right? No problem, we indicate two dynamic parts:

const formula2 = (n1, n2) => `=SUM(E${n1}:E${n2})`
// formula2(15, 24)  wil be   =SUM(E15:E24)

What's next? As we have said, we need to make jumps of 10 units, so we need a for loop that makes those jumps, create the formula dynamically and assign it to each of the cells.

for (let i = 0; i < 100; i = i + 10) {
    // The cell starts at 7, so we need to add it
    const f1 = formula1(7 + i)
    // Assign the value to the cell
    ss.getRange(6+i, 2).setFormula(f1)
}

Congratulations! you already have your formulas in the place you need and dynamically modified. You only need to fill it with each of the remaining formulas.

Sheets Formula Template

2 Answers2

Documentation