Error: Shared Formula master must exist above and or left of clone

[robophil]

I want to insert new rows dynamically into my sheet and have them inherit the formula used above.

  const workbook = await readWorkbook('./sample-sheet.xlsx')
  const worksheet = workbook.getWorksheet('Consumables')
  const row = worksheet.getRow(7)

  const column = 'O'
  const value = row.getCell(column).value
  console.log(`current value of ${column}`)
  console.log(value)
  console.log(row.getCell(column).formulaType)

  row.getCell(column).value = {
    sharedFormula: `${column}6`, result: 0
  }

  row.commit()

  await saveWorkbook(workbook, 'export.xlsx')

It logs this error

Error: Shared Formula master must exist above and or left of clone
 .....
......

This works for some columns. So my question is, how do I set a share formula? or better yet, how can I achieve this differently if there's an alternative

[williamluke4]

@robophil Did you manage to solve this?

[enzoferey]

So this was happening to me as well and I eventually found a solution that works well: just copy the whole value.

For example:

// Master
const masterCell = worksheet.getCell("A3")
masterCell.value = {
    formula: "A1+A2",
    result: myResult
}

// anywhere later...

// "Shared"
worksheet.getCell("A4").value = masterCell.value;

Now if you change the values or A1 or A2, both A3 and A4 will get updated. If you open the .xlsx export you will see that both cells use the same formula.

[robophil]

@robophil Did you manage to solve this?

I didn't actually. My previous company opted for https://github.com/SheetJS/js-xlsx with pro support.

[joobisb]

@robophil @enzoferey @williamluke4 i'm still facing this issue any solutions for this?..i have an excel with lot of formulas but i'm not changing anything just added a new column using spliceColumns and inserted few values in that column..these column doesnt have any formulas, but I'm getting the error mentioned here..

[taviroquai]

I'm also facing this problem. I wish the error at least tells me which cell? Thanks!

[joobisb]

@taviroquai
You can see the cell number in logs, they have added cell as part of error log in a commit 2 months ago. I think if you pull the latest version you can get it.
my issue was i was inserting a column in between another 2 columns and as a result the formula which were already existing on the sheet get dispersed.

[taviroquai]

@joobisb thanks for the info. I left original cells with formula intact and only fill new values/formula on empty cells. It was the only way to get around this.

[FraeuleinWild]

I finally realized what causes the error and it is really simple to overcome. Apparently dragging and dropping formulas in the excel template create these "slave-master relationships" that the library can't handle. The error does not occur if the templates formulas are inserted manually. So instead of writing in Cell A11: "=sum(A1:A10)" and then dragging the formula to cell B11 it is important to manually type in B11 "=sum(B1:B10)". The formulas generated by dragging and dropping are completely the same, but treated differently internally.

[davidmricardo]

If in the final excel you only care about the values you can test if it is a shared formula and grab the result:

row[22] = row[22]['sharedFormula'] ? row[22]['result'] : row[22];

Or you follow the link:

if (row[22]['sharedFormula']) {
var idx = row[22]['sharedFormula'].substr(1); // assuming it points to the same column wich is my case
row[22] = rows[idx][22];
}