Indirect formula not updating for new sheets

Indirect formula not updating for new sheets

Posted by: eulrich on 8 May 2024, 11:23 am EST

• 

  eulrich

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 8 May 2024, 11:23 am EST - Updated 8 May 2024, 11:28 am EST

  Steps to reproduce:

  1. In a workbook that only has Sheet1, add the text Sheet2!A1 in cell B2, then Sheet3!A1 in cell B3, then Sheet4!A1 in B4, etc.
  2. Create formula =INDIRECT(B2). This is referencing Sheet2 that does not exist yet and therefore gives a #REF error
  3. Create a new sheet, Sheet2. In Sheet2, enter a value in cell A1

  Expected result:

  1. After updating the value in Sheet2!A1, the value of the cell in step 2 above resolves form a REF error to match the cell value

  Actual result:

  1. The REF error does not resolve unless you create the formula again

  This behavior differs from Excel, where the REF error resolves after creating the sheet and entering a value

  

  1007×948 103 KB

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 8 May 2024, 10:20 pm EST

  Hi,

  I was able to replicate the issue at my end with the latest version of SpreadJS V17.0.8. I have escalated the issue to the concerned dev team for further investigation. The internal tracking id for the same is: SJS-24158

  As a workaround, you could calculate the sheet range when a sheet is inserted into the SpreadJS. Kindly refer to the following code snippet and the sample:

  spread.bind(GC.Spread.Sheets.Events.SheetChanged, function (sender, args) {
      // Check if sheet is inserted
      if (args.propertyName === "insertSheet") {
          for (let i = 0; i < spread.getSheetCount(); i++) {
              let sheet = spread.getSheet(i);
              if (sheet.name() !== args.sheetName) {
                  // Get the Range Used for Formulas
                  let formulasUsedRange = sheet.getUsedRange(GC.Spread.Sheets.UsedRangeType.formula);
                  // Convert the Range to Range String
                  var rangeStr = GC.Spread.Sheets.CalcEngine.rangeToFormula(new GC.Spread.Sheets.Range(formulasUsedRange.row, formulasUsedRange.col,
                      formulasUsedRange.rowCount, formulasUsedRange.colCount));
                  // Explicitly calculate the Range
                  spread.calculate(GC.Spread.Sheets.CalculationType.all, sheet.name() + "!" + rangeStr);
              }
          }
      }
  });

  Sample: https://jscodemine.mescius.io/share/RxxziDTbJUCU4SrRCUpFIA/?IsEmbed=false&Theme=Unset&PreviewDirection=0&IsEditorShow=true&IsExplorerShow=true&IsPreviewShow=true&IsConsoleShow=true&IsRunBTNShow=false&IsResetBTNShow=false&IsOpenInCodemineBTNShow=false&PanelWidth=20&PanelWidth=50&PanelWidth=30&defaultOpen={"OpenedFileName"%3A["%2Fsrc%2Fapp.js"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.js"}

  References:

  SheetChanged Event: https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.Events#sheetchanged

  calculate method: https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.Workbook#calculate

  rangeToFormula method: https://developer.mescius.com/spreadjs/api/modules/GC.Spread.Sheets.CalcEngine#rangetoformula

  getUsedRange method: https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.Worksheet#getusedrange

  Regards,

  Ankit

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 12 May 2024, 6:15 pm EST

  Hi,

  The devs have mentioned that this is not a common case for formula calculation and even the Microsoft Excel’s policy is inconsistent.

  For example, in Microsoft Excel, when you add a “Sheet2”, then the formula is not calculated and it shows “#REF”, and only if you enter some value in the Cell A1 of the “Sheet2”, then it calculates the formula.

  The devs have asked to use the workaround that I earlier suggested to you. Let us know if you face any issues with the provided workaround.

  Regards,

  Ankit