When a formula contains a reference to an external file/sheet/cell, the parser ignores the external file reference and only parses the sheet/cell reference. If the index of the original sheet is greater than the maximum index of the sheets of the current workbook, an IndexOutOfBoundException occurs. Exemple : WorkbookA has two sheets named Sheet1 & Sheet2 WorkbookB has only one sheet named Sheet1 In workbook B : The formula '[WorkbookA]Sheet1!$A$1' will be correctly parsed but will point to cell A1 in WorkbookB. the formula '[WorkbookA]Sheet2!$A$1' will fail to parse because it will point to the second sheet of WorkbookB, wich doesn't exist. If WorkbookB contains 2 formulas : A2 = '[WorkbookA]Sheet1!$A$1' A3 = '[WorkbookA]Sheet2!$A$1' Debuging the A2 cell record shows : Ptg(0)=org.apache.poi.hssf.record.formula.Ref3DPtg [sheetIx=2 ! $A$1] So it makes evaluation to fail also. The attached sample shows a formula that evaluate in the wrong workbook.
Created attachment 22625 [details] Sample code and workbook to reproduce bug
Fixed in svn r699761. Quite a lot of stuff needed to be done to get your test code working. Formula rendering was fixed so that external references get rendered with workbook name (as Excel does) The formula parser can now parse external references properly (as long as the same workbook+sheet combination already exists in the LinkTable). HSSFWorkbook probably still needs modifying to allow creation of new external references. The formula evaluator was extended to allow evaluation of formulas across multiple workbooks (see TestWorkbookEvaluator.testEvaluateMultipleWorkbooks()). We probably need to add examples for this functionality to the POI documentation. junits added