External Links Hiding in Named Ranges
Named ranges are, by a wide margin, the place people least expect to find an external link — and the place it's most likely to survive a cleanup pass. A named range can point at another workbook entirely, and it will keep doing so silently, since nothing about using that name in a formula elsewhere looks any different from using a normal internal range.
Why this is a genuinely hidden location
When you search formulas for external references — with Ctrl+F, or by scanning for square brackets — you're searching the visible formula text. A formula like =SUM(RevenueForecast) gives no indication that RevenueForecast is a named range whose "refers to" is '[Forecast_v3.xlsx]Sheet1'!$B$2:$B$50. The external part is one layer removed from anywhere a normal search looks.
How to check manually
- Open Formulas → Name Manager (or
Ctrl+F3). - Widen the Refers To column so full paths are visible rather than truncated.
- Read every single entry, looking for a file path, a
.xlsx/.xlsmextension, or square brackets. - Note that scope matters — a name can be scoped to the workbook or to an individual sheet, and sheet-scoped names won't always be obvious from the default Name Manager view depending on which sheet is active.
This works, but it's manual reading of every row with no shortcut — on a workbook with dozens of names, it's easy to skim past the one that matters.
What Sheetopsy checks specifically
Sheetopsy's scan explicitly includes every defined name in the workbook, checking each "refers to" value for an external file reference — the same thing the manual Name Manager walkthrough above does, done automatically and reported with the exact name and source file, alongside external references found in formulas. It also checks formulas, since the two hiding spots are related but distinct — a workbook can have external references in one, the other, or both.