Cannot get correct cell value when referencing names defined with evaluate

Cannot get correct cell value when referencing names defined with evaluate

Posted by: yyoshimura on 14 June 2021, 4:54 pm EST

• 

  yyoshimura

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 14 June 2021, 4:54 pm EST - Updated 29 September 2022, 6:33 am EST

  Hi,

  We found that under some circumstances, the cell value cannot be fetched correctly. Please refer attachment for reproduction(See CalcResult.xlsm).

  We have a workbook with following names defined.

  _F0101: =Sheet1!$B$2:$C$2
  
  _F0102: =Sheet1!$B$3:$C$3
  
  CalcResult: =EVALUATE(Sheet1!$A$1)

  

  1104×860 24.5 KB

  The formula in Sheet!$A$1 is [_F0101*_F0102].
  
  At cell G1 and H1, we are referencing the result of name [CalcResult] using the following formula.

  =IFERROR(INDEX(CalcResult,1,1),0)

  When workbook is opened in Excel, we can see that G1 and H1 has the correct value 20000 and 120000. But when we try to get the G1 and H1 value using the library, we can only get 0 and 0 due to IFERROR. We are using the latest version V4.1.1 by the way.

  

  1009×589 12.2 KB

  We hope that the value should be the same as Excel.
  
  Please help to investigate this issue. Thank you.

  Regards

  gcexcelperformance.zip

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 15 June 2021, 6:42 pm EST

  Hello,

  We too can observe the difference with the 4.1.2 builds and escalated this to the developers.
  
  We will let you know as soon as we get an update on this.
  
  [Internal Tracking ID: DOCXLS-4292]

  Regards,
  
  Prabhat Sharma.

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 1 August 2021, 3:06 am EST

  Hello,

  As per the developers in your file, there is a definedName which name is “CalcResult”, its content is “=EVALUATE(Sheet1!$A$1)”, the “EVALUATE()” is not a function, it is a MACRO.

  GCExcel doesn’t support MACRO, so the result is an error. They suggest you change the definedName’s content as “=_F0101*_F0102”, then GcExcel can work correctly.

  Please find the changed file as “CalcResult2.xlsx”.

  Regards,
  
  Prabhat Sharma.
  
  CalcResult2.xlsm.zip

• 

  yyoshimura

  • Post Options:
  • Link
    Copy

  Posted 9 August 2021, 10:55 am EST

  Thank you for letting us know that EVALUATE() is a macro function.
  
  We’ll consider other ways to achieve the same.