Created attachment 31385 [details] Patch (and test) Referencing a syntactically correct named range that does not exist should result in a cell with a #REF! error. However, it creates an exception. I have submitted a patch that works for INDIRECT() reference which solves my immediate need, but the exception for a direct reference is pretty "deep" in the stack: I suspect that there should be a unique exception created that is caught by the formula evaluator, which would then set the error code properly. Note that the patch includes: 1) code fix; 2) working test for INDIRECT reference; 3) commented out test for direct reference.
FYI, the direct case can be done by returning "new ParseNode(ErrPtg.REF_INVALID);" instead of throwing the Exception. Note however that this will change what was fixed in bug 47312 as with this the parser is less strict and will allow invalid formulas that it does reject now. While the change makes POI a bit more do whatever Excel does it actually makes working with the lib less fun in some cases as the error is much more hidden in a simple #REF! instead of a detailed error message in an Exception.
Perhaps a compromise would be to log to the POI Logger when an invalid reference is found. Otherwise, we would need to write a second Formula Parser or modify the existing to let the user chooser what kinds of errors it will propagate and what kinds of errors will raise an exception. Alternatively, we could write a formula evaluator that steps through one layer at a time.