INDEX function array form calculation error

Aspose.Cells Product Family
1

Hi,

I have an example Excel workbook here that uses the INDEX function with arrays TestCase09.zip (7.0 KB)

I use the following code to get the value of cell D482:

var workbook = new Workbook("TestCase09.xlsx");
workbook.CalculateFormula();
Worksheet worksheet = workbook.Worksheets["Sheet1"];
string s1 = worksheet.Cells["D482"].ToString();

The string s1 shows Aspose.Cells .NET version 22.3.0 is calculating the cell value as an error #VALUE.
However, when I open the workbook in Microsoft Excel for Microsoft 365 it shows the cell value as TRUE.
Can you please have a look at what could be the reason for the difference?

Many thanks!

2

@llawryy,

Please notice, I am able to reproduce the issue as you mentioned. I found the Excel workbook uses the INDEX function with arrays. I use the code to get the value of cell D482 which gives “#VALUE”. Moreover, I also tested opening your file into MS Excel 2007 and 2016 versions, both show/calculate as “#VALUE”. Only MS Excel 365 shows it as “TRUE”. Anyways, I have logged a ticket with an id “CELLSNET-50730” for your issue. We will look into it soon.

Once we have an update on it, we will let you know here.

3

Thanks @Amjad_Sahi

It sounds like returning TRUE may be related to the new Excel’s dynamic array behaviour, which is not available in earlier versions of Excel. Please keep me updated with any conclusions to the issue. Thanks.

4

@llawryy,

Sure, we will keep you posted with any updates once available.

5

@llawryy,

This is to inform you that your issue has been resolved now. The fix will be included in the next release (Aspose.Cells for .NET v22.5) which is scheduled in the second week of May 2022. You will also be notified when the next version is released.

6

The issues you have found earlier (filed as CELLSNET-50730) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi

7

Thank you. I’ve tested Aspose.Cells v22.5.0 and confirm it has been fixed.

8

@llawryy,

Thanks for your feedback.

Good to know that Aspose.Cells v22.5 fixes your issue. Feel free to write us back if you have further queries or comments.