Cannot get correct cell value when referencing names defined with evaluate

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

  • Posted 14 June 2021, 4:54 pm 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)



    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.



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

    Regards

    gcexcelperformance.zip
  • Replied 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.
  • Marked as Answer

    Replied 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
  • Replied 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.
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels