Free Support Forum - aspose.com

Range Names calculations

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;

I fixed the first bug and make the second feature in a few days.

Thanks a lot!

I hope you had a nice holiday.

Kind regards,

TLB