You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
fix(xls): a defined name that refers to another workbook is dropped, so formulas that use it show #NAME? #803
In an .xls file, a defined name that refers to another workbook is skipped on open ("Some defined names use references to other workbooks (external links) and were skipped."). Formulas that use the name are still read as formulas, so they calculate to #NAME?. Excel shows their saved results. Formulas that refer to the other workbook directly are kept as their saved values and show the right numbers. Only formulas that go through a name break.
Observed
Made in Excel 365:
Source.xlsx: Sheet1!A1:A3 = 10, 20, 30.
A new workbook with the name ExtRange = =[Source.xlsx]Sheet1!$A$1:$A$3 and B6 = =SUM(ExtRange), which gives 60.
Saved as Dest.xls (Excel 97-2003).
gridcraft-cli check Dest.xls (main fc45b7f, also with #464 merged)
mismatch Sheet1!B6 =SUM(ExtRange) expected 60 got #NAME?
Excel 365 opens Dest.xls (without updating links) with B6 = 60. The same workbook saved as .xlsx gives 60 in GridCraft once #464 is merged.
In Apache POI's test data and Damodaran's spreadsheets, 18 .xls files have such names, but gridcraft-cli check finds no #NAME? from them. The steps above are the reproduction.
Root cause
defined_names (crates/xlsx/src/xls/mod.rs:764) skips a name whose formula can't be decoded. The cell formulas that refer to it (PtgName) are decoded earlier, without regard to whether the name will be kept.
Suggested fix
When a cell formula uses a name the reader skips, keep that cell's saved result as a value, as is already done for formulas with external references. A full fix would read the name as a reference into the linked workbook with its cached values (SUPBOOK/CRN), the .xls counterpart of #464.
Activity
XLS: formulas that use a skipped defined name keep their saved results (