Calculations that contain range names do not seem to work when using CalculateFormula.
When I try to run following example (found at http://www.aspose.com/Community/Forums/17792/ShowPost.aspx):
Excel excel = new Excel();
Names names = excel.Worksheets.Names;
Name name = names[names.Add("Test")];
name.RefersTo = "=4+5-1";
excel.Worksheets[0].Cells["B3"].Formula = "=Test";
excel.CalculateFormula();
An "Aspose.Excel.ExcelException: Error in calculating cell B3 in Worksheet0" is thrown.
I also need other support with named Ranges:
Named Range: Name: Col1, RefersTo: =Sheet1!$A$1:$A$10
Named Range: Name: Row1, RefersTo: =Sheet1!$A$1:$G$1
Then for cell B2 Formula: =(Col1 Row1)
Then Cells["B2"].Value should get the value of the intersection of the two ranges (in this case the value in A1 should be returned).
Code:
Excel excel = new Excel();
Names names = excel.Worksheets.Names;
Name name = names[names.Add("Col1")];
name.RefersTo = "=Sheet1!$A$1:$A$10";
name = names[names.Add("Row1")];
name.RefersTo = "=Sheet1!$A$1:$G$1";
excel.Worksheets[0].Cells["A1"].PutValue(10);
excel.Worksheets[0].Cells["B2"].Formula = "=(Col1 Row1)";
excel.CalculateFormula();
int resultShouldBe10 = excel.Worksheets[0].Cells["B2"].IntValue;