I ran across this from a user file today, and hadn't seen it before. Chart series definitions default to just cell address ranges, but can be set to named ranges instead with some clicking and typing in the UI, which makes charts more dynamic.
However, for unknown reasons not documented in Excel help as far as I can see, the named range reference has to be specified as: 'workbook file name.xlsx'!named_range however, when saving, the XML doesn't store the file name, but rather: [0]!named_range which of course is not recognized by POI's formula parsing. When opening the file back up in Excel, it replaces [0] with the file name again in the formulas. My question is, does it seem reasonable, when the expression fails to parse otherwise, to check if it starts with [0]! and if so, strip that off the input and try again? Could do this up front and not do a second pass, but I don't know if there is valid syntax that could start with that string somehow. This would just be for parsing, it wouldn't update the stored value, as that's needed for the next time Excel opens the file. Not quite sure how to support creating charts using this syntax in POI - I don't have to solve that problem for my current task, but it's an interesting one to consider. I suppose a user could just set the saved formula above and it would work. That would need documentation I suppose, but this Excel functionality is only documented in non-MS places. I suspect it is a side effect they didn't plan for, but have to support because it's being widely used. Greg
