Created attachment 22223 [details] Example referenced in the description I reported something similar in https://issues.apache.org/bugzilla/show_bug.cgi?id=45353 but the stack trace is totally different, so I thought to make a new ticket. Sorry if its a dupe. When using named cells with the following formula (where tappab, tapp, pfy1 and totall are named cells): =IF(tappab<0;-21;IF(tapp+pfy1<>totall;-11;0)) I get the error: Exception in thread "main" java.lang.IllegalArgumentException: Specified colIx (1012) is out of range at org.apache.poi.hssf.record.formula.RefPtgBase.setColumn(RefPtgBase.java:144) at org.apache.poi.hssf.record.formula.RefPtgBase.<init>(RefPtgBase.java:61) at org.apache.poi.hssf.record.formula.RefPtg.<init>(RefPtg.java:35) at org.apache.poi.hssf.model.FormulaParser.parseIdentifier(FormulaParser.java:277) at org.apache.poi.hssf.model.FormulaParser.parseFunctionOrIdentifier(FormulaParser.java:229) at org.apache.poi.hssf.model.FormulaParser.parseSimpleFactor(FormulaParser.java:468) at org.apache.poi.hssf.model.FormulaParser.percentFactor(FormulaParser.java:433) at org.apache.poi.hssf.model.FormulaParser.powerFactor(FormulaParser.java:420) at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:628) at org.apache.poi.hssf.model.FormulaParser.additiveExpression(FormulaParser.java:725) at org.apache.poi.hssf.model.FormulaParser.concatExpression(FormulaParser.java:693) at org.apache.poi.hssf.model.FormulaParser.comparisonExpression(FormulaParser.java:650) at org.apache.poi.hssf.model.FormulaParser.Arguments(FormulaParser.java:405) at org.apache.poi.hssf.model.FormulaParser.function(FormulaParser.java:308) at org.apache.poi.hssf.model.FormulaParser.parseFunctionOrIdentifier(FormulaParser.java:227) at org.apache.poi.hssf.model.FormulaParser.parseSimpleFactor(FormulaParser.java:468) at org.apache.poi.hssf.model.FormulaParser.percentFactor(FormulaParser.java:433) at org.apache.poi.hssf.model.FormulaParser.powerFactor(FormulaParser.java:420) at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:628) at org.apache.poi.hssf.model.FormulaParser.additiveExpression(FormulaParser.java:709) at org.apache.poi.hssf.model.FormulaParser.concatExpression(FormulaParser.java:693) at org.apache.poi.hssf.model.FormulaParser.comparisonExpression(FormulaParser.java:650) at org.apache.poi.hssf.model.FormulaParser.Arguments(FormulaParser.java:405) at org.apache.poi.hssf.model.FormulaParser.function(FormulaParser.java:308) at org.apache.poi.hssf.model.FormulaParser.parseFunctionOrIdentifier(FormulaParser.java:227) at org.apache.poi.hssf.model.FormulaParser.parseSimpleFactor(FormulaParser.java:468) at org.apache.poi.hssf.model.FormulaParser.percentFactor(FormulaParser.java:433) at org.apache.poi.hssf.model.FormulaParser.powerFactor(FormulaParser.java:420) at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:628) at org.apache.poi.hssf.model.FormulaParser.additiveExpression(FormulaParser.java:709) at org.apache.poi.hssf.model.FormulaParser.concatExpression(FormulaParser.java:693) at org.apache.poi.hssf.model.FormulaParser.comparisonExpression(FormulaParser.java:650) at org.apache.poi.hssf.model.FormulaParser.parse(FormulaParser.java:751) at org.apache.poi.hssf.model.FormulaParser.parse(FormulaParser.java:113) at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateCell(HSSFFormulaEvaluator.java:346) at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.internalEvaluate(HSSFFormulaEvaluator.java:338) at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCell(HSSFFormulaEvaluator.java:183) at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:278) at NamedCellComplicatedFormulaTest.main(NamedCellComplicatedFormulaTest.java:20) In the example I have attached, the formula is in A2, and the named cells are in the first row. Note: this is in 3.1Final.
Fixed in svn r674953 Excel allows named ranges to have names which look like cell references. They seem to be distinguished only by the allowable values for row and column. For instance, "FY2008" is definitely a cell reference and cannot be a named range. "A70123" definitely not a cell reference and might be a named range. POI was interpreting 'pfy1' as a cell reference (also incorrectly calculating the column index to be 1012 - it would really be 10997). The fixed code now checks the apparent column ('pfy') and row ('1') for maximum BIFF8 range ('IV' and '65536') to decide whether to treat the identifier as a named range. There are all sorts of complexities related to this issue. For example dots are legal in named range names, but also may be used instead of the colon range operator. In BIFF12 (Excel 2007) the sheet boundaries change, so 'pfy1' becomes a valid cell reference, and presumably an invalid named range name.
I've back-ported some fixes for column number <-> letter conversion from the ooxml branch to trunk. Hopefully this helps