Formula returs #REF! after rows deletion

Formula returs #REF! after rows deletion

Posted by: pooja.bansal on 15 March 2022, 10:12 pm EST

• 

  pooja.bansal

  • Post Options:
  • Link
    Copy

  Posted 15 March 2022, 10:12 pm EST

  Hi,
  
  We are using farpoint in our desktop application.

  Original custom Formula - =Palkat!$J$7

  Sequence 1 -
  
  form.InpRptSpread.Sheets[sheetNo].RemoveRows(row, count);

  Sequence 2 -
  
  IExpressionSupport2 data = Sheets[sheetNumber].Models.Data as IExpressionSupport2;
  
  string str3 = data.UnparseFormula(0, 0, customName2);

  Output of formula(str3) - Palkat!#REF!

  Due to the code at sequence 1 , the piece of code at sequence 2 returns error in the formula.

  Solution tried -

         form.InpRptSpread.Sheets[sheetNo].AutoCalculation = false;
              form.InpRptSpread.Sheets[sheetNo].RemoveRows(row, count)
              form.InpRptSpread.Sheets[sheetNo].AutoCalculation =true;
  

  But it doesn’t update the formula, still the same error exists.
  
  Please guide how to get the updated formula after row deletion operation in Spread .Net

• 

  avnish.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 20 March 2022, 9:03 pm EST

  Hi,

  It is the designed behavior and is similar to the behavior in Excel, if you delete a structural part like a sheet, row, or column that contains references used in a formula then #REF error will be shown.
  
  You can refer to the following post for ways to avoid this error.
  
  https://www.spreadsheetweb.com/prevent-ref-error-in-excel/

  One of the ways is using the INDIRECT function, =INDIRECT(“Sheet2!$A$1”) will show the value that is now in place of the deleted A1 cell.

  Regards,
  
  Avnish