Every Link in the Contract Register Still Clicked, Four of Them Had Never Worked Once, and No Formula on the Sheet Could See a Single Target

The register lives at X:\Legal\Register\Contract Register.xlsx. Twenty rows, one per signed contract, 4,005,800.15 of committed spend, and in column C the name of the signed PDF with a Ctrl+K hyperlink on it so anyone can open the contract in one click. It had worked that way for three years.

Over a weekend, IT retired the X: share and moved everything to SharePoint.

Nothing about the workbook changed. Nobody edited a cell. Every check anyone knew how to run still passed, because every check anyone knew how to run reads cell values, and the part that broke is not a cell value.

A hyperlink made with Ctrl+K is not in the cell. It is an object in the worksheet's hyperlink collection, anchored to a cell, holding an address of its own. The cell holds a piece of text — Ashdown MSA 2024.pdf — and that text is all a formula, a filter, a sort key or Find & Replace can ever read. The address sitting behind it is in a different structure entirely, and Excel gives you exactly one way to look at it: right-click one cell, Edit Hyperlink, read the box, close it, repeat nineteen times.

What this covers. Everything here behaves the same in Excel 2016, 2019, 2021, 2024, Microsoft 365 and Excel for Mac, with two exceptions noted where they arise: TEXTBEFORE and TEXTAFTER are 365 and 2024 only, and FORMULATEXT needs 2013 or later. HYPERLINK, LEN, LEFT, RIGHT, MID, FIND, SEARCH, SUBSTITUTE, TRIM, REPT, COUNTA, COUNTIF, COUNTIFS, SUMIFS, SUMPRODUCT, IFERROR, IFNA, XLOOKUP, INDEX/MATCH, UNIQUE, FILTER, SORT, IF, SWITCH, ISNUMBER and LET are used as they work everywhere. A contract register is the example because the value behind each link is written on the same row, but this is the same job as a drawing index, a policy library, a CAPA log, a case file list, a photo register or any sheet whose purpose is to be the place you click from.

1) The Link Is Not in the Cell

Select C2 and look at the formula bar. It says Ashdown MSA 2024.pdf. That is the whole contents of the cell: eighteen characters of text, no formula, nothing else.

The blue underline is drawn by a separate object. In the file format it is a relationship entry with three parts that matter — the address (where to go), the sub-address (where inside the target to land) and the display text (what the cell shows) — plus an optional screen tip. Only the third of those is the cell's value. The first two are not stored in any cell, are not returned by any function, and are not searched by anything in the Find dialog, whose "Look in" list offers Formulas, Values and Comments and has never offered hyperlinks.

This is not a quirk to file away. It is the reason a register of two hundred links cannot be repointed with Find & Replace, cannot be audited with COUNTIF, cannot be checked by anyone reading the sheet, and cannot tell you it is broken until somebody clicks.

on the real sheet, where column C carries the links:
=COUNTIF($C$2:$C$21,"X:\*")     → 0     fifteen targets begin X:\
=COUNTIF($C$2:$C$21,"*.pdf")    → 20    but the display text is intact
=COUNTA($C$2:$C$21)             → 20    every cell has something in it

The first result is the one worth sitting with. Fifteen of these links point at a drive that no longer exists, and the formula written to find exactly that returns zero — not because the formula is wrong, but because it is reading a different thing from the one that is broken.

🎯 Scenario: Anywhere a Ctrl+K link is the point of the sheet, put the address in a real column as text as well, even if you never show it. A column you can read is a column you can COUNTIF, sort, diff against last month and hand to somebody else. An object you cannot read is a liability that reports its own state only by failing in front of a person.

2) Every Check Passed, and None of Them Could Have Failed

The register was checked the Monday after the migration, by somebody sensible, in three ways.

"Are all twenty contracts still listed?" =COUNTA($A$2:$A$21) returns 20, =COUNTA(UNIQUE($A$2:$A$21)) returns 20, and the register total =SUM($F$2:$F$21) returns 4,005,800.15, which agrees with the commitments ledger to the penny. None of these could ever have moved, because the migration touched a file share and not a workbook.

"Does every row still have a document against it?" =COUNTIF($C$2:$C$21,"*.pdf") returns 20. Also guaranteed: the display text is a string that was typed once in 2023 and has not been touched since. It would still return 20 if every PDF in the company had been deleted.

"Do the links look right?" They look exactly as they looked in 2023 — blue, underlined, correctly named, in the right order. A hyperlink's appearance is driven by the cell's style, and the style is not connected to whether the address resolves. A dead link and a live link are pixel-identical until clicked.

So the three checks agreed the register was fine, and the register was 90.53% unusable. The gap is not a failure of diligence. Every question that was asked was a question about column C's values, and the thing that broke has no value in column C.

🎯 Scenario: When a check cannot fail, it is not a check. Before you trust one, ask what result would make it fail and whether that result is reachable at all — a COUNTA over a column of text typed three years ago is arithmetic on a constant, not verification.

3) The Drive Letter That Was Never a Place

Fifteen of the twenty addresses look like this:

X:\Legal\Contracts\Eastvale NEC4 Option A.pdf

X: is not a location. It is a name your machine has agreed, privately, to use for a location — a mapping made by a login script, a Group Policy preference or somebody's right-click, and made independently on every machine. Two people can open the same register and one link can work for one of them and not the other, with no difference in the file at all, and that has always been true of this register; the migration only made it uniformly false.

The largest single one is CTR-2205 at 975,300.00 — a Groundworks contract on NEC4 Option A, the document you need in your hand when the target cost is disputed, behind a link that now says Cannot open the specified file.

There are three ways to write where a file is, and only one of them is a fact about the world:

• X:\Legal\Contracts\… — a drive letter. Per-machine, per-login, silently different for everyone.
• \\greenfell-fs01\Legal\Contracts\… — a UNC path. The same string means the same place from every machine on the network, which is why the same register written this way would have survived until the server itself was retired.
• https://greenfell.sharepoint.com/sites/Legal/Contracts/… — a URL. The same string means the same place from anywhere, including phones and people outside the building, and it is what the addresses should have become.

🎯 Scenario: Search your own registers for ":\" before anything migrates. Every hit is a link whose correctness depends on a mapping you do not control and cannot see. Replacing a drive letter with a UNC path is a search-and-replace that costs an afternoon; discovering the problem the way this team did costs a weekend and a hundred phone calls.

4) The Four That Never Worked

Four filenames in this register contain a #:

Bramwell Hire #3 Schedule.pdf        46,250.75
Garrowby Piling #2.pdf              208,650.00
Lindow Drainage #1.pdf               88,315.50
Penhale LV #4.pdf                   197,340.90
                                    ----------
                                    540,557.15     13.49% of the register

In a hyperlink, # is the character that separates the target from a location inside the target — the bookmark separator. It is how Report.xlsx#Sheet2!A1 means "open Report.xlsx, then go to Sheet2!A1", and it is how a link to a web page jumps to a heading. Excel splits the address at the first # and puts everything after it in the sub-address, and it does that whether or not you intended a bookmark.

So the link on CTR-2202 does not hold what it appears to hold:

That link has never opened anything. Neither have the other three. They were made in 2023, they failed on the day they were made, and in three years nobody reported one, for a reason worth stating plainly: the message Excel shows for a truncated path is the message it shows for a file you lack permission to open, a file somebody has moved, and a server that is down. On a shared drive all four of those are ordinary weather. You shrug, you ask the person who sent it, you get the PDF by email, and you never once think the register is lying to you.

The fix is to encode the character rather than leave it: %23 in place of # in the address, which Excel resolves back to a literal # when it opens the file. Better, rename the file — a # in a filename is a small landmine for every system that handles URLs, not only Excel.

🎯 Scenario: Run one search for # across every filename in any folder a workbook links into, and do it today rather than at migration time. The same goes for %, & and +, which are all meaningful in a URL and all legal in a Windows filename — that combination is the whole problem.

5) Relative, and Relative to What

The five surviving links are the five that were made sloppily. They hold no drive, no server and no scheme:

..\Contracts\Calderbank SOW.pdf

When the target of a Ctrl+K link sits near the workbook, Excel stores the address relative to the workbook's own folder rather than absolutely. It is trying to be helpful, and usually is: move the workbook and its folder of documents together and every relative link still resolves, because the relationship between them has not changed.

That is exactly what happened here. The register sat in X:\Legal\Register\; ..\Contracts\ resolved to X:\Legal\Contracts\. The migration put the register in a Register folder and the PDFs in a Contracts folder under the same parent, and ..\Contracts\ re-resolved to the new location without anybody doing anything.

379,465.20 of contract value survived the weekend because the shape of two folders happened not to change. Not because relative links are better. If the migration had flattened the tree, or dropped the register one level deeper, or given the folder a different name, all five would have died with the other fifteen, and there would have been no survivors to notice.

One more thing decides how a relative address resolves, and almost nobody knows it is there. File ▸ Info ▸ Properties ▸ Advanced Properties ▸ Summary has a field called Hyperlink base. Set it, and every relative hyperlink in the workbook resolves against that string instead of the workbook's folder. It is a genuinely useful lever — a register that has to live in several places can carry one base and be repointed in one edit — and it is also an invisible reason for links that behave differently in two copies of the same file.

🎯 Scenario: If you inherit a workbook whose links work for you and not for a colleague, check Hyperlink base before anything else. It is one field, it is empty in most workbooks, and when it is not empty it is usually the answer.

6) What Ctrl+K Actually Stores

Press Ctrl+K on a cell and Excel writes four things, of which the dialog shows you three and the sheet shows you one.

Three consequences follow from that table, and all three are in this register.

Display text and target are independent. Edit the cell text and the address does not move; edit the address and the text does not move. CTR-2209 reads Ingleby Scaffold Rev C.pdf and points at Ingleby Scaffold Rev A.pdf, because somebody updated the register when Rev C was issued and updated the only part they could see. It opens. It has always opened. It opens the wrong contract, at 53,870.00, and no check in this article except one finds it.

Filling down copies the address unchanged. Drag a Ctrl+K cell down twenty rows and you get twenty links to the same file, with twenty different display texts if the text was part of a series. This is the single most common way a register acquires links that are confidently, uniformly wrong.

Find & Replace cannot reach the address. Ctrl+H over this sheet replacing X:\Legal\ with https://greenfell.sharepoint.com/sites/Legal/ reports zero replacements and changes nothing, which reads like the paths are already fine.

after the migration, of twenty links:
opened something                        5      379,465.20      9.47%
opened the document the cell names      4      325,595.20      8.13%
opened nothing                         15    3,626,334.95     90.53%
of those, never once worked             4      540,557.15     13.49%

🎯 Scenario: Treat display text as a caption, never as evidence. If a row needs to assert which revision is filed, that assertion belongs in a cell of its own that something can compare against the path — which is only possible once the path is in a cell, which is section 12.

7) HYPERLINK Is a Formula, and That Is the Whole Difference

=HYPERLINK(link_location, [friendly_name])

Two arguments: where to go, and what to show. It returns the friendly name as the cell's value and jumps on click. It exists in every version of Excel in use.

What matters is not what it does. It is where the target lives. With Ctrl+K the target is in an object beside the grid; with HYPERLINK the target is a string inside a formula, in the cell, in the calculation chain, in front of you. Everything follows from that one move:

There is one thing HYPERLINK does not do, and it is the same thing Ctrl+K does not do: neither checks that the target exists. =HYPERLINK("X:\nothing\here.pdf","Open") returns Open, displays as a link, and fails on click like any other. Excel has no native function that asks the file system a question. What HYPERLINK gives you is not validity — it is visibility, which is the thing you can actually build a check on.

🎯 Scenario: The rule is simple enough to apply without thinking about it: Ctrl+K for one link you are writing by hand; =HYPERLINK() for the second one and every one after. The moment a link is one of a set, it belongs in a formula.

8) Twenty Links from One Formula

Put the folder in one cell, the filenames in one column, and build the rest.

B1:  https://greenfell.sharepoint.com/sites/Legal/Contracts/

in the register, against a table named Register:
=HYPERLINK($B$1 & [@Document], [@Document])

That fills down twenty rows, and each row builds its own address from the same root. It is the difference between twenty facts and one fact used twenty times.

Where the filename is not stored but derivable, derive it:

=HYPERLINK($B$1 & [@Ref] & " " & [@Supplier] & ".pdf", [@Ref])

Where the document lives in an index on another sheet, look it up — and decide what an unmatched row should do rather than letting it produce a link to nowhere:

=LET(
   file, XLOOKUP([@Ref], Index[Ref], Index[File], ""),
   IF(file = "", "— no document filed —", HYPERLINK($B$1 & file, file))
 )

That IF is not decoration. Without it an unmatched lookup returns "", the address becomes the folder with nothing after it, and the cell renders as a live-looking link that opens a directory listing or nothing at all. IFNA and IFERROR do the same job in versions without LET. A row with no document should say so in words.

Two details that cost people an afternoon each:

• Spaces are legal in a path and awkward in a URL. For an http or https address, replace them: SUBSTITUTE($B$1 & [@Document], " ", "%20"). For a UNC or drive path, leave them alone — Windows handles them, and %20 there breaks the path.
• # needs encoding in either form, as section 4 explains: SUBSTITUTE(path, "#", "%23"). Nest the two substitutes when a folder can contain both.

🎯 Scenario: Build every register this way from the first row, not from the row where it starts hurting. The cost is one extra column and one cell; the saving is that you never again have to open a dialog to find out where a link points.

9) Jumping Inside the Workbook, and the Rename That Breaks It

HYPERLINK also navigates inside a file, using the # from section 4 deliberately:

=HYPERLINK("#'Contract Register'!A1", "Back to the register")
=HYPERLINK("#Summary!B4", "Go to the total")

The leading # means "this workbook". A sheet name containing a space needs single quotes around it, exactly as a formula reference would, and forgetting them is the usual cause of Reference is not valid.

Now the trap, and it is a real one. That target is a string, not a reference. Rename the sheet from Summary to Summary 2026 and every ordinary formula pointing at it updates automatically, because Excel tracks references through a rename. The HYPERLINK does not update, because Excel has no idea the text "#Summary!B4" was about a sheet. It is a string, and strings do not track anything. Click it and you get Reference is not valid — the same behaviour, for the same reason, as INDIRECT.

Use a defined name instead:

Formulas ▸ Define Name ▸ RegisterTop  =  'Contract Register'!$A$1
=HYPERLINK("#RegisterTop", "Back to the register")

A defined name holds a reference, so it follows the sheet through a rename, and the string "#RegisterTop" stays true. A navigation bar built on defined names is a navigation bar that survives the next reorganisation of the workbook; one built on sheet names is one more thing to remember to fix.

🎯 Scenario: Anywhere a workbook has a home screen or a menu sheet, build it on defined names. It costs one dialog per destination, once, and it removes an entire class of breakage that otherwise surfaces months later in front of whoever inherited the file.

10) The Links Nobody Asked For

Paste a URL into a cell and Excel makes it a hyperlink. Type one and press Enter and Excel makes it a hyperlink. This is AutoFormat As You Type ▸ Internet and network paths with hyperlinks, it is on by default, and on a sheet of imported data it means every one of forty thousand URLs arrives as its own hyperlink object.

That is not a cosmetic problem. Each object is a stored relationship in the file; a column of them adds megabytes, slows scrolling and repaint, makes the file slower to open, and turns an accidental click during ordinary navigation into a browser window. It is also why a workbook that holds nothing but text can be four times the size of the data in it.

• Turn it off: File ▸ Options ▸ Proofing ▸ AutoCorrect Options ▸ AutoFormat As You Type, untick the box.
• Undo one as it happens: Ctrl+Z immediately after the cell commits removes the link and keeps the text.
• Strip existing ones: select the range, right-click, Remove Hyperlinks. The text stays, the objects go. Note that this also clears the Hyperlink cell style, so a column of blue underlines becomes plain — which is the point.

The reverse case is worth knowing too. A column of raw addresses with no links at all is often better than a column of link objects: one =HYPERLINK() column beside it gives you clickability without the objects, and the raw column stays readable, sortable and auditable.

🎯 Scenario: On any sheet built from an import, strip hyperlinks as the last step of cleaning, alongside trimming spaces and fixing types. Forty thousand link objects is a performance problem that nobody will ever diagnose, because nothing about the sheet looks unusual.

11) Checking a Register Before Somebody Needs It

Excel cannot ask the file system whether a file exists. There is no ISFILE, HYPERLINK does not validate, and no formula will tell you a PDF was deleted last March. Accept that, and check the things that are checkable — which turns out to be most of what actually goes wrong.

What is checkable is shape: that every address starts from the root you expect, ends in the extension you expect, contains no character that will truncate it, and names the document the row claims to be about. Three of the four failures in this article are shape failures, and the fourth — a deleted file — is the rare one.

Once the addresses are in cells, all of it is one column of formulas:

root, in B1:
https://greenfell.sharepoint.com/sites/Legal/Contracts/

=LET(
   addr,  $E2,
   name,  TEXTAFTER(addr, "/", -1),
   SWITCH(TRUE,
     LEFT(addr, LEN($B$1)) <> $B$1,        "not under the root",
     ISNUMBER(SEARCH("#", name)),          "# will truncate this",
     RIGHT(addr, 4) <> ".pdf",             "not a PDF",
     name <> $D2,                          "name does not match the cell",
     "ok")
 )

TEXTAFTER(addr,"/",-1) takes the last segment — the -1 means "the last instance of the delimiter". Before 365 it is the old idiom, which is ugly and works everywhere:

=TRIM(RIGHT(SUBSTITUTE($E2, "/", REPT(" ", 200)), 200))

For a Ctrl+K register, none of this is available, and that is the whole argument of this article in one line: you cannot write the check, because you cannot read the thing being checked.

🎯 Scenario: A status column like this pays for itself the first time somebody adds a row. It catches the wrong root, the stray # and the revision drift at the moment they are introduced, by the person who introduced them, rather than in a dispute eighteen months later.

12) One Root Cell

Here is the entire fix, and it is smaller than any of the problems it removes.

B1:  https://greenfell.sharepoint.com/sites/Legal/Contracts/

C2:  =HYPERLINK(SUBSTITUTE($B$1 & [@Document], " ", "%20"), [@Document])
     filled down

One cell says where the contracts live. Twenty formulas say which one. The migration that broke this register would have been a single edit to B1 and a recalculation — and if the same register had held two hundred rows, it would still have been a single edit to B1.

Everything else in this article follows from that arrangement rather than needing to be remembered:

• Nothing is stored per-row except a filename, so fifteen addresses cannot disagree about the root, because there is only one root.
• The root is a cell, so Find & Replace reaches it — and so does anybody reading the sheet, which matters more.
• Each address is built where it is used, so filling down produces twenty different links, not twenty copies of one.
• The display text and the address are the same expression, so they cannot drift. CTR-2209 could not have happened: change the cell to Rev C and the link is to Rev C, because it is the same value.
• The check in section 11 becomes possible at all, because there is something in a cell to check.

The register above is worth 4,005,800.15. The difference between the version that survived the weekend and the version that did not is one cell and a formula that fits on one line.

🎯 Scenario: When you inherit a register of Ctrl+K links, do not repair them one dialog at a time. Extract the filenames, which are already in the cells, put the folder in a root cell, and rebuild the column as =HYPERLINK(). It is usually twenty minutes, it is the last time anyone does it, and it converts an object you cannot see into a formula you can.

13) Four Checks

Run these against a register whose addresses are in cells — column E in the example, column Address in yours. On a Ctrl+K register none of them can run, which is itself the first finding.

1. Does anything still point at the old root?

=SUMPRODUCT(--ISNUMBER(SEARCH("X:\Legal\", $E$2:$E$21)))     → 15

Any answer above zero is a count of links that depend on a drive letter. Run it before a migration, not after.

2. Will any address be truncated?

=SUMPRODUCT(--ISNUMBER(SEARCH("#", $E$2:$E$21)))              → 4

SEARCH treats ? and * as wildcards but # as an ordinary character, so this does what it looks like. Every hit is a link that fails on click and has probably been failing for years.

3. Does the cell name the file the address opens?

=SUMPRODUCT(--(TEXTAFTER($E$2:$E$21, "\", -1) <> $D$2:$D$21))  → 1

This is the only check in the article that finds CTR-2209, and it is the only failure mode that never produces an error message. A non-zero answer is a row where the register is confidently describing a document it does not open.

4. Is every address built from the one root?

=SUMPRODUCT(--(LEFT($E$2:$E$21, LEN($B$1)) <> $B$1))          → 0 when healthy

Leave this one in the workbook permanently. It returns zero for as long as the arrangement in section 12 holds, and stops returning zero the first time somebody pastes in a link from somewhere else.

14) Twelve Traps

1. A Ctrl+K hyperlink is an object, not a value. The cell holds display text; the address lives beside the grid where no formula, filter or sort can read it.
2. Find & Replace cannot touch a hyperlink address. Ctrl+H reports zero replacements on a sheet of broken links, which reads exactly like "nothing to fix".
3. COUNTIF over linked cells matches the caption, not the target. =COUNTIF(C2:C21,"X:\*") returns 0 on a register where fifteen targets begin X:\.
4. A drive letter is a per-machine mapping. X:\ means a different place, or no place, on every other desk in the building — including the one that opens the register next.
5. # in a filename truncates the address at the #. Excel reads the rest as a bookmark inside the target. Encode it as %23 or rename the file; %, & and + deserve the same suspicion.
6. The error for a truncated path is the error for a permissions problem, a moved file and a dead server. On a shared drive that is ordinary weather, so a permanently broken link can go years without being reported.
7. Relative links resolve against the workbook's folder, so moving the workbook alone silently changes where every one of them points — and moving it with its folder tree silently keeps them working, which is luck, not design.
8. Hyperlink base, in Advanced Properties, redefines what "relative" means for the whole workbook. It is empty in most files and it is the answer whenever two copies of one workbook behave differently.
9. Display text and address are independent. Editing the visible name of a document never repoints the link, which is how a register comes to name Rev C and open Rev A without ever erroring.
10. Filling a Ctrl+K cell down copies one address into every row. =HYPERLINK() filled down builds a different address per row; the link object does not.
11. HYPERLINK's target is a string, so it does not track renames. Rename a sheet and "#Summary!B4" breaks where a real reference would have updated. Point at a defined name instead.
12. Neither kind of link checks that the target exists. Excel cannot ask the file system anything. What you can check is shape — root, extension, illegal characters, name agreement — and shape is where almost all of it goes wrong.

Nothing in this story is a bug, and nothing in it is even careless. Ctrl+K is the obvious way to attach a document to a row, a drive letter is what the file explorer shows you, a # in Piling #2 is how the piling contractor numbers their schedules, and a relative path is what Excel chose on the team's behalf without mentioning it.

What makes it expensive is that a hyperlink is the only thing in a workbook that carries meaning and is not a value. Everything else on a sheet can be counted, compared, searched and printed. A link target can only be clicked, and clicking is something a person does, one at a time, months apart, usually at the exact moment they cannot afford for it not to work.

So the fix is not a better link. It is moving the target out of the object and into a cell, where it becomes an ordinary string in an ordinary column, subject to the same checks as everything else — and where the difference between a register that survives the next migration and one that does not is a single edit to a single cell.