Repository navigation
fix(formula): references to another workbook by file name or path are refused or #REF!, saved in a form Excel misreads, and shown as [1] #804
Description
Summary
In Excel, typing a reference to another workbook creates a link:
- Excel reads the value from that file, open or closed.
- It saves the formula as
[1]Sheet1!A1with anexternalLinkpart that holds the file's path. - The formula bar shows the file name, or the full path when the file is closed.
GridCraft (main fc45b7f, also with #464 merged) does none of this:
- The usual unquoted form is rejected.
- The other forms are accepted and give
#REF!or#NAME?. - The typed text is saved into
<f>as-is, with no link part, and Excel misreads it. - Links read from Excel files show as
[1]in the formula bar, so the user can't tell which file a formula reads.
#769 reports the #REF! for ='[b.xlsx]Sheet1'!$A$1 with password-protected files. #472 covers referencing another open workbook. #464 reads the cached values of existing links. This issue covers the rest: parsing, saving and display.
Observed
Source.xlsx (in …\links\) has Sheet1!A1:A3 = 10, 20, 30 and the name Rate = 0.05. In a new workbook:
| typed in A1 | GridCraft | written to the file | Excel 365 opening that file | typed in Excel 365, Source.xlsx closed |
|---|---|---|---|---|
=[Source.xlsx]Sheet1!A1 |
refused: "There's a problem with this formula: unexpected Sheet("Sheet1", None)" | – | – | – |
='[Source.xlsx]Sheet1'!A1 |
#REF! |
<f>'[Source.xlsx]Sheet1'!A1</f>, no externalLink part |
a link to Source.xlsx with no folder, missing file, #REF! |
– |
='C:\…\links\[Source.xlsx]Sheet1'!A1 |
#REF! |
<f t="array" ref="A1">'C:\…\links\[Source.xlsx]Sheet1'!A1</f> |
a link to C:\…\links[Source.xlsx]Sheet1, which doesn't exist; formula shown as 'C:\…\[links[Source.xlsx]Sheet1]links[Source.xlsx]Sheet1'!A1, #REF! |
10 |
=Source.xlsx!Rate |
#NAME? |
<f>Source.xlsx!Rate</f> |
a link to Source.xlsx, missing file, #NAME? |
– |
='C:\…\links\Source.xlsx'!Rate |
not tested | 0.05 |
Excel 365 also gave 60 for =SUM('C:\…\links\[Source.xlsx]Sheet1'!A1:A3) and 111 for ='C:\…\links\[Source.xlsx]P&L Data'!A1.
In a workbook that already links to Source.xlsx (saved by Excel, formula [1]Sheet1!A1):
- Formula bar. GridCraft shows
=[1]Sheet1!A1(display::edit_text,crates/engine/src/display.rs:39). Excel shows='C:\…\links\[Source.xlsx]Sheet1'!A1. - Names. For a name over the link,
gridcraft-cli infogivesrefersTo==[1]Sheet1!$A$1:$A$3. Excel'sName.RefersTogives='C:\…\links\[Source.xlsx]Sheet1'!$A$1:$A$3. - Typed forms in that workbook. GridCraft doesn't map
='[Source.xlsx]Sheet1'!A1to the existing link: it gives#REF!and writes the typed text. Excel 365, opening that file, matches it to the existing link and shows 10.
Suggested fix
- Parse
[Book.xlsx]Sheet!A1,'path\[Book.xlsx]Sheet'!A1,Book.xlsx!Nameand'path\Book.xlsx'!Nameas references into a linked workbook.- Match an existing link by path, or by file name when no path is given.
- Otherwise add a new link with the path. It has no cached values until the file is read, so the reference is
#REF!for now, as in Excel when the file is missing.
- Keep the link's path in the model (XLSX: formulas into linked workbooks calculate from the values saved with the link #464's
ExternalBookhas none yet). Print it for display (formula bar, Name Manager) and write[n]only to the file. - Never write a file name or path inside
<f>. - Reading the linked file itself (Excel's Update Values / Edit Links) is a separate step.
Activity
Merge issue storytold#804 external workbook parser fix
Linked workbooks: references typed by file name or path go through a …
iluebbe commentedon Oct 23, 2026
#829 checked
Fixes the parsing, saving and display parts. Every typed form in the issue becomes a reference through a link (
[n]in the file), the link part it writes opens in Excel, and Excel reads the values through it. It needs a real rebase ontomain, and it carries #464, which is still open.Setup: PR head 48d4342 built at its head (based on e6b6b34, 690 commits behind; seven files conflict),
mainat a1c8454, Windows 11,gridcraft-cli runwith aSource.xlsx(Sheet1!A1:A3 = 10, 20, 30, sheetP&L Data!A1 = 111, nameRate= 0.05) written by GridCraft into a temporary folder, Excel for Microsoft 365 through COM opening the saved workbook read-only, crate tests.main='<dir>\[Source.xlsx]Sheet1'!A1[1]Sheet1!A1=SUM('<dir>\[Source.xlsx]Sheet1'!A1:A3)SUM([1]Sheet1!A1:A3)='<dir>\[Source.xlsx]P&L Data'!A1'[1]P&L Data'!A1=[Source.xlsx]Sheet1!A2[1]Sheet1!A2(same link, by file name)='<dir>\Source.xlsx'!Rate,=Source.xlsx!Rateinvalid 3-D reference), #NAME?[1]!RateNums=='<dir>\[Source.xlsx]Sheet1'!$A$1:$A$3,=SUM(Nums)[1]Sheet1!$A$1:$A$3Rateis #REF! in Excel too when the same formula is typed into a new workbook through COM with thisSource.xlsxclosed, so that row says nothing against the link part. In Excel the formulas andNumsshow the full path, andLinkSourceslists<dir>\Source.xlsx. In GridCraft the Name Manager anddocument.inspectshow the path;<f>anddefinedNamein the file hold only[1], and theexternalLinkpart hassheetNames, theRatename andrefreshErrorsheets with afile:///target.Nearby cases:
[source.XLSX]sheet1!A3and'C:/…/Source.xlsx'!Rate(forward slashes) reuse link[1];[Other.xlsx]Sheet1!A1adds[2];Data.v2!Ratestays local when a sheetData.v2exists (7);[Source.xlsx]!A1is refused with "a reference to another workbook needs a sheet name";SUM([Source.xlsx]Sheet1:Sheet2!A1)is kept as[1]Sheet1:Sheet2!A1.Problems: none found in the change. Reading the linked file itself (Update Values) is out of scope, as the issue says, so a new link stays #REF! in GridCraft; Excel fills in the values when it updates the link.
Merging: conflicts with
mainat a1c8454 incrates/calc/src/eval.rs,crates/engine/src/cmd/formulas.rs,crates/engine/src/display.rs(main added the editor's apostrophe for text, next to the PR's linked-file display),crates/formula/src/printer.rs(main moved sheet quoting intogridcraft_core::sheet_needs_quotes),crates/model/src/lib.rs,crates/model/src/workbook.rsandcrates/ui-egui/src/formula_bar.rs. These need a rebase, and #464, which this branch includes, has to land or be rebased with it.Tests (at the PR head): the new tests pass (
linked_workbooks.rswith 4 tests,another_workbook_by_file_name_or_path_parses,another_workbook_without_a_sheet_is_not_a_cell_reference,linked_workbook_sheet_ranges_round_trip,a_new_link_follows_the_kept_ones, the calcreferences_into_linked_workbooks_*tests);cargo test -p gridcraft-calc -p gridcraft-engine -p gridcraft-formula -p gridcraft-model -p gridcraft-xlsx: calc 62 (1 ignored), engine 286 lib + 50 integration, formula 26, model 14, xlsx 173, no failures; clippy for those five crates andcargo fmt --all -- --checkclean. The ui-egui changes (formula bar, Show Formulas) were not built or tested.Harrison-Booth commentedon Oct 28, 2026
Re-tested on current main (11bb673),
gridcraft-clirelease build on Windows, and desktop Excel (COM) for the Source.xlsx / link files.Excel-authored link (A1
='C:\wintest\t5\[Source.xlsx]Sheet1'!A1= 10, A2=SUM(...A1:A3)= 60): GridCraft opens it withWarning: links to external workbooks were kept; they are not followed or refreshed, shows the cached 10 and 60, uses them in a new=SUM(A1:A2)(70), and writes[1]Sheet1!A1back with the link part. That part works.Typing links in GridCraft:
=[Source.xlsx]Sheet1!A1is still refused ("unexpected Sheet("Sheet1", None)").='C:\wintest\t5\[Source.xlsx]Sheet1'!A1, the same inside SUM, and='[Source.xlsx]Sheet1'!A1all give#REF!and are saved as plain<f>text with noexternalLinkpart (the first ast="array"), as in the issue.Result: partly fixed. Reading and keeping existing links works; typing a link still doesn't. I did not check the formula bar text or names in the UI, or reopen the saved files in Excel.