We have a multi-sheet workbook used for calculating population growth estimates. Sheet A has a semi-complicated formula which is basically a nested IF(OR()) function with 3 if blocks. The formula references cells in another sheet which are formulas to other columns in a sheet. The value in excel appears to work as expected. But after loading into Excel Worksheet (Infragistics.Documents.Excel.16.2) in memory all the values appear to calculate correctly except the ones referencing across worksheet.
Example: We have a detail sheet and a summary sheet. Summary sheet has the following Formula: =IF(OR(Detail!$A$28, Detail!$A$29), Detail!E$16, IF(Detail!$A$30, Detail!E$17, IF(Detail!$A$31, Detail!E$18, Detail!E$17))) where Detail A28 - A31 are string values like TRUE or FALSE that evaluate to boolean. The other refernced columns E16, E17, E18 etc are functions adding other columns in the Detail sheet which I can inspect and all run correctly. But the function on the Summary Sheet that references the Detail Sheet just return a zero, which isn't possible if it looks at ANY of the other fields.
This problem is new after upgrading from version 9.x to 16.x and I'm sure there have been a lot of changes since then. Any pointers? I am unable to find ANYTHING referencing this sort of issue. I also have not been able to replicate it using test spreadsheets.
Hello Jesse,
I have tested this behavior in all supported version and cannot reproduce it. The cell value is always correct after I import the workbook. As you said you were not able to reproduce this with a test workbook, therefore I can only assume this is somehow related to your original workbook. Can you send us this workbook so we can try to reproduce this issue at our side? If we succeed we will be able to look further into this, otherwise there is not much we can do.
Looking forward to your reply and workbook provided.
Attached are 2 spreadsheets. The Estimate2 being the template used to fill in the data and extract the values. The 2016Ritzville spreadsheet is after the template is loaded, filled with data, and exported to disc. All values on export show up as expected, but the values in memory do not work.
The Example Formula I'm using can be found @ Summary D 4.
=ROUND(IF(OR(Detail!$A$28, Detail!$A$29), Detail!E$12, IF(Detail!$A$30, Detail!E$13, IF(Detail!$A$31, Detail!E$14, Detail!E$13))), 6)
Which evaluates to 0 in memory. See image: https://www.dropbox.com/s/z5v7sy9jhhyac1q/DetailIfOr.PNG?dl=0
If we break down the formula (images below) it should read
=ROUND(IF(OR(FALSE, FALSE), 0.840876, IF(FALSE, 0.840876, IF(TRUE, 0.843876, 0.843876))), 6)
IMAGES @ RUNTIME
DetailA28: https://www.dropbox.com/s/nnaau0lzgyfa3rs/DetailA28.PNG?dl=0
DetailA29: https://www.dropbox.com/s/si4n7v53b1vg5d0/DetailA29.PNG?dl=0
DetailA30: https://www.dropbox.com/s/ovrp22jf3lhqgrh/DetailA30.PNG?dl=0
DetailA31: https://www.dropbox.com/s/jx86pwm85infmzp/DetailA31.PNG?dl=0
DetailE12: https://www.dropbox.com/s/vwx6dyaxkkjjwi1/DetailE12.PNG?dl=0
DetailE13: https://www.dropbox.com/s/f7h3ma4rawcges9/DetailE13.PNG?dl=0
DetailE14: https://www.dropbox.com/s/asv5b7zcmg7hu4c/DetailE14.PNG?dl=0