#REF! errors with cross-sheet formulas

#REF! errors with cross-sheet formulas

Posted by: daniel on 14 May 2019, 1:14 pm EST

• 

  daniel

  • Post Options:
  • Link
    Copy

  Posted 14 May 2019, 1:14 pm EST

  Hi,

  We are running into a weird issue. Here are the high-level steps of our code:

  1. Create new workbook
  2. Suspend paining
  3. Load cell data from db
  4. Resume painting

  What we see now is that all formulas that reference cells within the same sheet are fine, but ones that reference cells in other sheets show up as #REF!

  If you double click into the #REF! cells you can see the formula is correct, but pressing enter still results in #REF!. If you copy the formula from the error cell and paste it into a new cell you get the expected result.

  Any idea what might be happening here? We are using 12.1.0.
  
  We’ve tried calling suspendCalcService and resumeCalcService before and after loading the data respectively, but still nothing.

  Daniel

• 

  manish.gupta

  

  • Post Options:
  • Link
    Copy

  Posted 14 May 2019, 10:19 pm EST

  Hi Daniel,
  
  Could you please share how the cell content is loaded from the DB?

  Are you loading content for all sheets or just for one?

  If you may share the demo sample depicting your issue it would be a great help to investigate the issue.

  You may share the sample with dummy data or if you would not like to share the sample in the public forum, you may create a support ticket at SupportOne(https://supportone.componentone.com/login).

  Regards,
  
  Manish Gupta

• 

  daniel

  • Post Options:
  • Link
    Copy

  Posted 14 May 2019, 11:45 pm EST

  Thanks Manish - I’ve submitted a support ticket.

• 

  datta

  • Post Options:
  • Link
    Copy

  Posted 16 May 2019, 12:07 am EST

  Hi I am also facing same issue. When formula is loaded from database I get #REF! error though cell is showing correct formula when you click on it. Any update on this?

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 16 May 2019, 5:06 pm EST

  Hi,

  The issue could arise if you are adding sheets one by one because the sheets are dependent on one another.
  
  To fix the issue, you need to recalculate the formulas after adding all the sheets. Please refer to the following code snippet:

  spread.sheets.forEach((sheet)=>{
      sheet.recalcAll(true);
  });
  

  If the issue persists, please share a small sample replicating the issue.

  ~sharad

• 

  datta

  • Post Options:
  • Link
    Copy

  Posted 17 May 2019, 8:10 pm EST

  Thanks!! It worked