Reading Cell value as #Value! while value is correct in Excel

Aspose.Cells Product Family
21

@Amjad_Sahi @ahsaniqbalsidiqui
we tried s2!B4 and find that the value is read correctly with old version after formula is reset.
Please find the screen recording
“Dropbox - File Deleted - Simplify your life”

22

@guptadeeptarun,
Thank you for the sample video. We will analyze it and share feedback later.

23

@guptadeeptarun,
The video does help us to find the cause of the difference between your test and ours. In fact when you reset the formula in excel 365, the formula was set as Array formula automatically which directs the calculation to another complete different logic. That is, the formula data has been changed completely after you reset it. Only excel 365 has this functionality so we did not get the reset formula work in common ms excel. For such kind of array formula, we can calculate it correctly even with old versions.

24

@ahsaniqbalsidiqui
Can you please elaborate more on this
"In fact when you reset the formula in excel 365, the formula was set as Array formula automatically which directs the calculation to another complete different logic. That is, the formula data has been changed completely after you reset it. "

Also to make it simple can you let us know what change we need to do in excel to be able to read by the old version only without having to reset the formula ?

25

@guptadeeptarun,
We have noted your comments and will share our feedback soon.

26

@guptadeeptarun,
To get details about the difference of normal formula and array formula for the specific cell, you have to compare the the data saved in the template file by yourself.

Without resetting the formulas, we are afraid there is no way to make old versions work with those formulas. However, instead of doing that in ms excel manually, you may try to reset formulas with our APIs and then calculate them:

cells = workbook.getWorksheets().get("s2").getCells();
int max = cells.getMaxDataRow();
for(int i=0; i<=max; i++) {
Cell c = cells.checkCell(i, 1);
if(c != null && c.isFormula()) {
c.setArrayFormula(c.getFormula(),1,1);
}
}
workbook.calculateFormula(false);
System.out.println(cells.get("B4").getValue());
27

The issues you have found earlier (filed as CELLSJAVA-43210) have been fixed in Aspose.Cells for Java 20.7. This message was posted using Bugs notification tool by Amjad_Sahi