We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Aspose.Cell is not Evaluating INDIRECT function correctly when used within another formula

The INDIRECT function by itself works fine. But used within another formula and if the referencing cell is in a different sheet, it is not evaluating right. See my attached code and test file for reproducing this issue.

Thank you

Tomy

static void Main(string[] args)

{

Workbook book = new Workbook();

book.Open(@"C:\temp\TEST200.xls", FileFormatType.Excel97To2003);

Worksheet sheet = book.Worksheets[0];

string a1_f1 = sheet.Cells["A1"].Formula; // "=INDIRECT(F7)"

string a1_v1 = sheet.Cells["A1"].StringValue; // Returns "A1" - Right value

string a2_f1 = sheet.Cells["A2"].Formula; // "=OFFSET(INDIRECT(F7),5,2)"

string a2_v1 = sheet.Cells["A2"].StringValue; // Returns 22 - Right value

book.CalculateFormula();

string a2_f2 = sheet.Cells["A2"].Formula; // "=OFFSET(INDIRECT(F7),5,2)"

string a2_v2 = sheet.Cells["A2"].StringValue; // Returns 0 - Wrong value

// Try substituting the value of the formula "INDIRECT(F7)"

sheet.Cells["A2"].Formula = "=OFFSET(A1,5,2)";

book.CalculateFormula();

a2_v2 = sheet.Cells["A2"].StringValue; // Still return 0

// Try this now

sheet.Cells["A2"].Formula = "=OFFSET(Sheet2!A1,5,2)";

book.CalculateFormula();

a2_v2 = sheet.Cells["A2"].StringValue; // Return 22 - Right value.

// So the issue is if INDIRECT function is used within other formula.

// In Excel INDIRECT function preserve WorksheetName reference, Aspose is not.

}

Hi,

Please try the attached version. I have tested with your scenario using your code and template file, it works fine.

Thank you.

I tested the attached code with your new version. Still not working.

I am getting the right value before calling CalculateFormula method. After calling CalculateFormula it returns 0.

Please check.
Thank you, Tomy

Hi,

Please make sure that you are using my attached version v4.9.1.11. Please remove reference of older Aspose.Cells.dll in VS.NET solution explorer and then add reference to the new fix v4.9.1.11.

I have tested and I get 22 value before and after calculating the formulas for your test code with your template file.

Thank you.

Still not working. Still returning 0 after calling the CalculateFormula with V4.9.1.11.

I removed some lines from the code to make it more clear. Run the code and check the value of a2_v1 and a2_v2 they are not matching, but they should.

static void Main(string[] args)

{

Workbook book = new Workbook();

book.Open(@"C:\temp\TEST200.xls", FileFormatType.Excel97To2003);

Worksheet sheet = book.Worksheets[0];

// Before calling CalculateFormula

string a2_f1 = sheet.Cells["A2"].Formula; // "=OFFSET(INDIRECT(F7),5,2)"

string a2_v1 = sheet.Cells["A2"].StringValue; // Returns 22 - Right value

book.CalculateFormula();

// After Calling CalculateFormula

string a2_f2 = sheet.Cells["A2"].Formula; // "=OFFSET(INDIRECT(F7),5,2)"

string a2_v2 = sheet.Cells["A2"].StringValue; // Returns 0 - Wrong value

}

Hi,

It still give me 22 using your current code which is fine i think.

Kindly create a simple console demo application with the latest fix, zip it and post it here to reproduce the issue, we will check it soon.

Thank you.

Demo application attached. The test xls file is in bin\debug folder. Unzip the files and run the "Prefix.Formatter.Test.exe" to see the results.

Thank you, Tomy

Hi,

Please try the attached version (latest fix) v4.9.1.13 and let us know if you still find the issue. I have tested it with your file using your project, it works fine.

Thank you.

Yes. Its working now. Does this version has all the updates from V4.9.1.10 ?

Thank you, Tomy

Hi,

Yes, sure it has all the previous updates/fixes, features and enhancements etc.

Thank you.