Named ranges breaking overnight for no apparent reason is the kind of frustration that makes you question everything you thought you knew about spreadsheets. This user's problem, identical named ranges in separate tabs suddenly triggering #NAME? errors and invalid hyperlinks, is deeply familiar to anyone who has pushed a workbook beyond its intended design. The named ranges are still there, unchanged. The formulas still work when re-entered. But the moment the source workbook is opened, the external links collapse. That is not user error. That is a structural weakness in how traditional spreadsheets handle scope and dependencies.
Let's be direct about what is happening. Each tab contains a named range called "SHIP" that points to a single cell. In a workbook with multiple sheets, a name defined without an explicit scope defaults to workbook-level visibility. But the screenshot shows these names are scoped to their individual sheets. That is a deliberate choice, and it should work. The problem is that external workbooks linking to those names do not always resolve sheet-scoped references reliably, especially when the source workbook is closed or reopened. Microsoft's name manager treats them as valid, but the link engine treats them as ambiguous. The result is a daily reset of broken references that no amount of manual re-entry can permanently fix.
The practical takeaway is this: if you rely on named ranges to feed data into other workbooks, you are trusting a system that was never built for that job. Named ranges are a convenience for human readability, not a robust data pipeline. They break when files move, when scopes conflict, or when the calculation engine decides to re-evaluate in a different order. The user's workaround, re-entering formulas each morning, is a bandage on a design flaw. The real solution is to stop treating spreadsheets as databases. Move the source data into a single, structured table that external references can target by cell address or by a dedicated lookup function. Or better, adopt a tool that treats named references as first-class connections, not fragile aliases.
This is not about blaming the user. It is about recognizing that spreadsheets were built for a world where data stayed in one file. That world no longer exists. If your workflow depends on reliable cross-workbook links, you need a system that handles scope, recalculation, and external dependencies as core features, not afterthoughts. The name manager is a relic. The future is a data model where a cell named "SHIP" means the same thing everywhere, every time, without requiring you to rebuild it at sunrise.