Linked Workbooks: The Group Summary That Was 133,250.00 Short Because Three Links Never Updated

The tab below is the front page of a board pack. Six entities, one row each, and not a single number typed by a human: every cell in C2:F7 is a formula pointing into that entity's own budget workbook on the finance share.

It says total spend is 1,284,600.00 across 235 people.

Both numbers are wrong. Total spend is 1,417,850.00 and there are 257 people. Three of the six source files were re-saved during July — Nordics, Iberia and Benelux hired between them 22 people and added 133,250.00 of cost — and none of that reached this sheet, because the last person to open the board pack was asked whether to update links and clicked Don't Update.

Nothing on the tab is red. Nothing says #REF!. Every cell holds a number that was true on some real day, which is exactly why nobody catches it: cost per head reads 5,466.38 where the truth is 5,516.93, a gap of under one percent, and cost per head is the one ratio anybody actually checks.

This article is about links to other workbooks — what they really are, the four different ways they lie to you, and how to build a workbook where a stale source announces itself instead of hiding.

Tip: Copy the table below into a blank sheet starting at A1 to follow along. Everything here works in any desktop version of Excel; where a feature needs Microsoft 365 or a recent version, it is marked where it appears.

1) What Excel Actually Means by a Link

An external reference looks like a slightly overdressed cell reference:

='C:\Finance\Budgets\2026\[Budget_Nordics_2026.xlsx]Summary'!$D$18

Read it right to left and it is ordinary. $D$18 is the cell. Summary is the sheet. [Budget_Nordics_2026.xlsx] is the workbook, in square brackets. Everything in front is the folder it lives in. The single quotes wrap the path and workbook and sheet together, and they are there because the path contains characters — spaces, most often — that would otherwise end the reference early.

Three things change how that same link is displayed:

• The source is open. Excel drops the path and shows =[Budget_Nordics_2026.xlsx]Summary!$D$18. Same link, shorter clothes.
• The source is closed. The full path comes back. This is why a formula bar seems to grow a folder path overnight — nothing changed except that somebody closed the other file.
• The source is on OneDrive or SharePoint. The path is a URL: ='https://contoso.sharepoint.com/sites/Finance/Shared Documents/[Budget_Nordics_2026.xlsx]Summary'!$D$18. It behaves the same way, with one addition covered in section 9 — the reader has to have permission to the source, and yours is not theirs.

The part that matters for the rest of this article is what Excel keeps on your side. Your workbook stores a copy of the last value it read from each linked cell, inside your own file. That cache is what makes a linked workbook openable at all when the source is on a share you are not connected to. It is also the entire reason a stale link looks like a healthy one.

2) The 133,250.00, and Why Every Check Passes

When a workbook containing links is opened, Excel either shows a bar across the top —

SECURITY WARNING  Automatic update of links has been disabled.   [Enable Content]

— or, depending on version and settings, the older dialog:

This workbook contains links to one or more external sources that could be unsafe.

If you trust the links, update them to get the latest data. Otherwise, you can
keep working with the data you have.

        [ Update ]      [ Don't Update ]      [ Help ]

Don't Update is not "leave the cells blank". It means keep serving the cached values, and it is a completely reasonable thing to click when you are opening a file to read one sentence out of it. The problem is that the decision is invisible five minutes later, and it survives being saved.

Here is what the three stale rows are actually hiding:

Nordics on its own is understated by more than the entire CEE entity costs. And the headcount column is stale in exactly the same rows, so:

That last line is the one that hurts. The money is stale and the heads are stale together, so the ratio between them barely moves. A sanity check made of two numbers from the same stale source cannot detect that the source is stale.

The one column that can catch it is column G. Each source workbook keeps its own last-saved date in a cell, and the summary links to it like everything else — so when a link stops updating, its date freezes too. Three rows say 28/06/2026 while three say 31/07/2026, and that mixture is the alarm:

=COUNTIF(G2:G7,"<>"&$G$2)                    → 3
=SUMPRODUCT((G2:G7<>$G$2)*F2:F7)             → 420,450.00

Three sources are on a different vintage from the first one, and they carry 420,450.00 — nearly a third of the reported total. Put it on the sheet in words, above the table, where it cannot be scrolled past:

=IF(COUNTIF(G2:G7,"<>"&$G$2)>0,
    "STALE: "&COUNTIF(G2:G7,"<>"&$G$2)&" of 6 sources not refreshed",
    "All six sources on the same cycle")

🎯 Scenario: You have just been handed a summary like this and have no idea when it last updated.

Do not start by checking the formulas — they are fine, and they will look fine all day. Open Data → Edit Links, press Update Values, and watch whether any number on the sheet moves. If one does, the version you were about to present was wrong, and so was the version presented last month. Then add the stamp column, because "somebody remembered to press Update" is not a control.

3) Data → Edit Links: Six Statuses and Four Buttons

Data → Queries & Connections → Edit Links (in recent versions, Data → Workbook Links) lists every source your cells point at, one row per file, however many thousand cells reference it. The Status column is the useful part:

And the buttons, in the order they are worth reaching for:

• Update Values — re-reads the source now. On a closed source, Excel opens it invisibly, takes what it needs and closes it again.
• Change Source — repoints every reference to a file, in one go. This is the fix after the finance share is reorganised, and it is thousands of times better than find-and-replacing paths by hand.
• Open Source — opens the other workbook, which is how you check whether the number you are getting is really the number it holds.
• Break Link — turns the formulas into their current values. Permanent; section 7.

Two settings sit behind this dialog and decide what happens without you:

• Startup Prompt (button, bottom left) sets, per workbook, whether readers are asked, always updated silently, or never updated. "Don't display the alert and don't update" is how a file becomes permanently stale for everybody who opens it.
• File → Options → Advanced → General → Ask to update automatic links is the same choice per user, for every file they open. Turned off by a colleague years ago, it explains why their copy never shows the prompt and yours does.

4) When Excel Asks About Links That Are Not There

A recurring, genuinely maddening one: Excel asks to update links every time the file opens, and Edit Links lists nothing, or lists a file that no cell mentions. Links live in more places than cells:

• Defined names. A name whose Refers to is ='C:\Users\jsmith\Desktop\[Budget v7.xlsx]Sheet1'!$B$2, left behind by a sheet copied in from somewhere. Open the Name Manager (Ctrl+F3), widen the Refers to column, and read down it. The filter dropdown has Names with Errors.
• Data validation. A dropdown whose Source is a range in another workbook.
• Conditional formatting. A rule whose formula reaches into another file.
• Chart series. A chart pasted from another workbook keeps pointing at that workbook's data, and charts are not searched by Ctrl+F.
• PivotTables and Power Query. A cache built from an external range, or a query whose path is baked in.
• Shapes and objects. A shape with a formula in it, or an embedded object.

Two ways to hunt them down:

In Excel. Ctrl+F, search for [, set Within: Workbook, Look in: Formulas, and press Find All. Every cell whose formula contains a workbook name comes back in one list. Then check the Name Manager separately, because Find will not look there.

Outside Excel, when you want the definitive answer. Close the file, copy it, rename the copy to .zip and open it. Inside:

• xl/externalLinks/ — one externalLink1.xml per linked workbook, containing the cached values that section 1 described
• xl/externalLinks/_rels/externalLink1.xml.rels — the actual path or URL each one points at

That folder is the truth. If it is empty, the workbook has no external links, whatever the dialog says.

🎯 Scenario: The prompt appears, Edit Links is empty, and you have already looked at every formula.

Take the zip route. Nine times in ten it is a defined name pointing at somebody's Desktop, and the fix is to delete the name in the Name Manager — after checking nothing uses it, which =COUNTIF cannot tell you but Find All on the name text can.

5) The Functions That Cannot Read a Closed Workbook

Most formulas do not care whether the source is open — the cached values are enough. A specific group does care, and fails in a way people misread as a broken file:

So this works in the morning, when everyone has the regional file open, and breaks at 18:00 when they have gone home:

=SUMIFS('C:\Finance\Budgets\2026\[Budget_Nordics_2026.xlsx]Detail'!$F:$F,
        'C:\Finance\Budgets\2026\[Budget_Nordics_2026.xlsx]Detail'!$B:$B,"Salary")

The reason is worth knowing rather than memorising: the conditional-aggregate family is implemented against a live range, not against a block of values, so there is nothing in the cache for it to work on. Rewrite the same logic as arithmetic and it reads the cache happily:

=SUMPRODUCT(('C:\Finance\Budgets\2026\[Budget_Nordics_2026.xlsx]Detail'!$B$2:$B$500="Salary")
            *'C:\Finance\Budgets\2026\[Budget_Nordics_2026.xlsx]Detail'!$F$2:$F$500)

Note the bounded ranges. $B$2:$B$500, not $B:$B — with an external reference, every cell you name is a cell whose value gets copied into your file, so linking whole columns is how a 400 KB summary becomes a 40 MB one.

INDIRECT deserves its own sentence, because people reach for it to build paths from text and it cannot work: INDIRECT assembles a reference at calculation time, and a reference that did not exist when the file was saved has nothing cached behind it. =INDIRECT("'"&A2&"[" &B2&"]Summary'!$D$18") returns #REF! on every closed source, every time. If you are tempted to build link paths from a table of file names, that is Power Query's job, not INDIRECT's.

6) The Four Ways a Link Lies

1. Stale. The whole of section 2. The value is real, it is just from the wrong day. No error, no colour.

2. #REF!. The file moved, was renamed, or the sheet inside it was renamed. This is the honest failure: it is loud, it is obvious, and it is the one people complain about most — while the silent one costs far more.

3. Zero, wearing a disguise. Somebody found the #REF! distracting and tidied it:

=IFERROR('C:\Finance\Budgets\2026\[Budget_Nordics_2026.xlsx]Summary'!$D$18,0)

The day that file moves, Nordics contributes 0.00, the total drops by 240,900.00, and the sheet is immaculate. IFERROR around an external reference converts a visible failure into an invisible one. If you must catch it, catch it as words: =IFERROR(link,"SOURCE MISSING") puts text in a numeric column, which breaks the total noisily — which is the point.

4. The right number from the wrong row. The worst of the four, and the least known. Insert two rows at the top of a source workbook's Summary sheet while your file is open and Excel quietly rewrites your link from $D$18 to $D$20; do it while your file is closed and it cannot, so your link still says $D$18 — which is now Non-Salary, or a subtotal, or a blank. You get a plausible number belonging to a different line, with no error and nothing stale about it.

🎯 Scenario: One region's figure moved by an amount nobody can explain, and no formula was edited.

Open the source and look at what $D$18 holds today. Then ask when rows were last inserted there. The fix is not to correct the cell reference but to stop referring to a position at all: give the cell in the source a defined name — Nordics_TotalSpend — and link to the name. ='C:\Finance\Budgets\2026\Budget_Nordics_2026.xlsx'!Nordics_TotalSpend follows the cell wherever it moves, in both files, open or closed. This is the single best habit in the article.

7) Break Link: What It Does, and What You Cannot Undo

Break Link replaces every formula pointing at that source with the value it currently shows. The links are gone; the numbers stay.

It is the right thing to do in three situations: sending the workbook outside your organisation, archiving a period that must never change again, and inheriting a file whose sources no longer exist. It is the wrong thing to do to a live model.

Four things to know before pressing it:

• It cannot be undone. Not by Ctrl+Z, not reliably. Save a copy first, every time.
• It converts what is in cells. Links inside defined names, chart series and objects can survive it — which is why the update prompt sometimes keeps appearing on a workbook whose links you "broke".
• If the button is greyed out, look for a protected sheet in the workbook; unprotect, break, re-protect.
• A broken link is a frozen link. The number is now correct-as-of and will never say so out loud. Type the date into the cell next to it, in words: "Values as at 31/07/2026, links broken."

8) Building One That Cannot Go Quietly Wrong

Everything above is failure. This is the design that avoids it.

One direction only. Sources feed the summary; the summary feeds nothing back. Two workbooks linking to each other means the answer depends on which was opened first, and there is no ordering that fixes it.

One import sheet. Every external reference in the workbook lives on a single tab called Import, one block per source, and nothing else in the file links out — the rest of the model reads Import. When you need to know what this workbook depends on, there is one place to look, and Change Source has one place to change.

A stamp beside every source. Column G of the example. Link one cell from each source that changes when the source changes — its last-saved date, its period label, its own grand total — and put the staleness formula from section 2 at the top of the sheet. A link that cannot be refreshed then says so in English instead of quietly showing you June.

Names, not addresses. Section 6. Link to Nordics_TotalSpend, never to $D$18.

A path everyone can reach. C:\Users\jsmith\Desktop\ works for exactly one person on exactly one machine. A UNC path (\\server\finance\Budgets\) or a SharePoint URL works for the team. The most common "it works for me" bug in Excel is a link to a local drive.

Or stop using links. Power Query does the same job better in almost every respect: Data → Get Data → From File → From Workbook, land it in a table, and the connection is refreshed when you press Refresh and never behind your back. When the file moves, it fails loudly with the path in the message rather than serving last month's numbers, and one query parameter holding the folder path repoints all of them at once. The only thing links do that Power Query does not is give you a single cell instantly — which, for six numbers on a board pack, is a fair reason to keep them, and a bad reason to build a hundred.

9) Six Ways This Goes Wrong in Practice

1. Clicking Don't Update out of habit. It is the safe-sounding button, it is remembered by nobody, and it is where this article started. If you open a linked workbook to read it, that is fine. If you open it to present it, press Update.

2. Sending a linked workbook to someone outside. They cannot reach your finance share, so they see your cached values with no way to refresh them, or a wall of #REF!. Break the links on a copy, or send a PDF.

3. Assuming a SharePoint link travels. It does — and it takes the permissions with it. A colleague without access to the source gets a sign-in prompt or an error, on a file that works perfectly for you.

4. Editing the source's structure with the dependent file closed. Inserting rows, deleting columns, renaming a sheet. All three are silent from the source's side, and all three land in the dependent workbook as either #REF! or, worse, the wrong cell.

5. Paste-Special-Values over half a summary. Half the sheet is now frozen and half is live, they look identical, and next month's numbers will be a mixture of two months. If you are freezing values, freeze the whole block and label it.

6. Letting links accumulate. Every external reference is a file that must exist, at a path, for a person with permission, forever. Every quarter, open Edit Links and ask of each source whether it still earns its place. The ones that do not are why opening the workbook takes forty seconds.

Conclusion

The one sentence worth keeping: a link to a closed workbook shows you a value your own file remembered, not a value it just read. Every strange thing links do follows from that. They keep working when the source is deleted. They keep showing numbers after they stop updating. They never go blank, because there is always something cached to show.

So the control cannot be "check the numbers look right" — the numbers always look right; that is the failure mode, not the exception. It has to be something structural: a stamp from each source, sitting on the sheet, doing the checking every time the file is opened. Three cells reading 28/06/2026 next to three reading 31/07/2026 is a question anybody can answer in five seconds. A total of 1,284,600.00 is not.

Want to practise the formula side of this? Several exercises in the app are built on exactly these shapes — conditional counts used as alarms rather than answers, lookups that find a record by name instead of by position, and totals that have to survive somebody rearranging the rows underneath them.