How can I handle #REF errors

How can I handle #REF errors

Posted by: mlatona on 15 September 2021, 3:12 pm EST

• 

  mlatona

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 15 September 2021, 3:12 pm EST

  Hi. I load a specific spreadsheet. The spreadsheet have four tabs. In the first tab ‘Quelle’ (‘source’) includes the source data. The third tab shows data from the first tab. The second tab ‘Ziel’ (‘target’) shows data from the third tab. The attached sample includes two spreadsheets. One spreadsheet have no rows and columns in the first tab. I create this programmatically. This spreadsheet shows the #REF errors. The other spreadsheet have rows and columns in the first tab. This spreadsheet works correctly.
  
  RefError.zip

• 

  avinash.pathak

  • Post Options:
  • Link
    Copy

  Posted 16 September 2021, 2:45 am EST

  Hi,

  Thanks for the sample. We are able to replicate the issue at our end hence we have escalated this issue to the concerned team for further investigation. We will update you regarding this as we get any information from the team. The internal ID for this issue will be SJS-10111.

  Regards
  
  Avinash

• 

  avinash.pathak

  • Post Options:
  • Link
    Copy

  Posted 23 September 2021, 6:07 am EST

  Hi,

  The team informed us that, The custom name src is Quelle!$A$6:$JH$303 , but in the formula =index(src,3,3) the looking area will be Quelle!$A$6:$JH$6 because the sheet only has 6 rows. We need to refresh the expression tree after set the row count.

  The following codes works:

  sheet.setRowCount(400);
  sheet.recalcAll(true); // needs recalcAll with true.
  

  Regards
  
  Avinash

• 

  mlatona

  • Post Options:
  • Link
    Copy

  Posted 26 September 2021, 6:06 pm EST

  Thank you. It works.