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.
}