It’s 3:12 PM. You’re updating the Q2 vendor payout sheet when Sarah Chen from Finance messages: ‘Can you pull the total approved amount for Acme Corp using the ‘VendorName’ reference? It’s in cell B7.’ You search B7 — it’s blank. You check Formulas > Name Manager. There it is: VendorName, pointing to =Sheet2!$D$4. But D4 says ‘BetaTech Inc.’ — not Acme Corp. You freeze. The report’s due in 47 minutes.
The Setup
We’re working with a real procurement dataset — 9 vendors, mixed statuses, and three sheets: Dashboard, VendorList, and Payouts. The team uses ‘Names’ to link logic across sheets, but nobody documented what each one points to — or whether it’s even still valid.
| Vendor ID | Company Name | Status | Approved Amount ($) | Last Updated |
|---|---|---|---|---|
| V-7021 | Acme Corp | Active | $45,200 | 2024-03-15 |
| V-7022 | BetaTech Inc. | On Hold | $12,850 | 2024-02-28 |
| V-7023 | Nexus Logistics | Active | $89,600 | 2024-04-02 |
| V-7024 | Orion Labs | Inactive | $3,200 | 2023-11-19 |
| V-7025 | Stellar Dynamics | Active | $67,150 | 2024-03-22 |
| V-7026 | Veridian Systems | Active | $21,900 | 2024-04-05 |
| V-7027 | Lumina Group | On Hold | $14,330 | 2024-02-10 |
| V-7028 | TerraForge Solutions | Active | $55,720 | 2024-03-30 |
| V-7029 | Axiom Dataworks | Inactive | $8,410 | 2023-09-14 |
This data lives in VendorList!A2:E10. Meanwhile, Dashboard!B7 contains this formula: =SUMIF(VendorName,"Acme Corp",ApprovedAmount). And yes — both VendorName and ApprovedAmount are names. Not cells. Not ranges. Names.
The Challenge
You need to know what ‘Name’ means in Excel — not as a menu label, but as a functional object that sits between formulas and data. Most people think ‘Name’ equals ‘named range’. That’s like thinking ‘car’ equals ‘steering wheel’. It’s part of it — but not the whole system.
Here’s what makes this tricky:
- You can’t see Names in the worksheet — they don’t appear in A1 or anywhere else. They’re invisible unless you look in Name Manager (Ctrl + F3) or use FORMULATEXT().
- A Name can point to a cell, a range, a formula, a constant, or even an external workbook — and Excel won’t warn you if the target gets deleted or moved.
- Names have scope: Workbook-level vs. Worksheet-level.
VendorNameon Dashboard might mean something totally different thanVendorNameon Payouts — and Excel won’t flag the conflict. - Some Names are built-in (
CellWidth,Recalc) or created by add-ins — and they don’t show up unless you uncheck ‘Hide built-in names’ in Name Manager.
(Trust me — I learned this the hard way when a $200K forecast error traced back to a hidden Name called CurrentYear that pointed to =YEAR(TODAY())+1 instead of TODAY().)
Walking Through It
Let’s trace VendorName step-by-step — not just where it points, but what it *means* at each layer.
Step 1: Find the Name
Press Ctrl + F3 — or go to Formulas > Name Manager. In the list, find VendorName. Double-click it.
Its ‘Refers To’ field shows: =VendorList!$B$2:$B$10. So far, so simple. It’s a range.
| Before: VendorName definition |
|---|
| =VendorList!$B$2:$B$10 |
Step 2: Check its scope
In Name Manager, look at the ‘Scope’ column. It says ‘Workbook’. That means any sheet can use VendorName — no sheet prefix needed. If it said ‘Dashboard’, you’d have to write Dashboard!VendorName elsewhere.
Now go to VendorList!B2:B10. That’s where the company names live — exactly matching our table above.
Step 3: Follow the formula that uses it
Go to Dashboard!B7: =SUMIF(VendorName,"Acme Corp",ApprovedAmount).
So what’s ApprovedAmount? Back to Name Manager. Its definition is: =VendorList!$D$2:$D$10. That’s the Approved Amount column — same rows, same scope.
Excel treats those Names as direct proxies. When you type VendorName, Excel silently swaps in VendorList!$B$2:$B$10 before calculating. No recalc lag. No warning if B2:B10 shifts.
| Before: SUMIF with Names | After: Excel’s internal expansion |
|---|---|
| =SUMIF(VendorName,"Acme Corp",ApprovedAmount) | =SUMIF(VendorList!$B$2:$B$10,"Acme Corp",VendorList!$D$2:$D$10) |
Step 4: Test what happens if the Name breaks
Delete row 3 in VendorList (BetaTech Inc.). Excel shifts B2:B9 up — but VendorName still points to $B$2:$B$10. Now it includes an empty cell (B10) and skips the last real entry. Your sum drops by $12,850 — silently.
Here’s the counterintuitive tip: Names don’t auto-adjust when rows/columns shift — unless you define them with OFFSET or INDIRECT. Most teams assume they do. They don’t.
To fix it, change VendorName to: =OFFSET(VendorList!$B$2,0,0,COUNTA(VendorList!$B$2:$B$100),1). Yes — it’s longer. But now it expands/shrinks with real data.
The Result
After correcting both Names and validating scope, Dashboard!B7 returns $45,200 — correctly. More importantly, we now know what ‘Name’ means: it’s a symbolic alias with scope, evaluation timing, and dependency behavior — not just a label.
| Vendor | Approved Amount ($) | Status |
|---|---|---|
| Acme Corp | $45,200 | Active |
| Nexus Logistics | $89,600 | Active |
| Stellar Dynamics | $67,150 | Active |
| Veridian Systems | $21,900 | Active |
| TerraForge Solutions | $55,720 | Active |
What Could Go Wrong
Here are three exact mistakes we’ve debugged in production files — with how to spot and fix each:
Mistake #1: Duplicate Names with Different Scopes
You see TotalSales in Name Manager — but it appears twice: once scoped to ‘Summary’, once to ‘Workbook’. When you type =TotalSales on Summary sheet, Excel uses the local version. On another sheet? It uses the workbook one. No error. Just inconsistent results.
Fix: Sort Name Manager by ‘Scope’, then delete duplicates. Or rename one to Summary_TotalSales.
Mistake #2: Names Pointing to Deleted Sheets
A Name shows =ArchivedData!$A$1:$Z$100 — but the ‘ArchivedData’ sheet was deleted last month. Excel doesn’t break the Name. It just returns #REF! wherever used — often buried inside nested formulas.
Fix: In Name Manager, click ‘Filter’ > ‘#REF! errors’. Then either restore the sheet or update the reference.
Mistake #3: Using Names in Array Formulas Without Ctrl+Shift+Enter (Legacy)
You define Top5Values as =LARGE(DataRange, {1;2;3;4;5}). On older Excel versions, this only works if entered as an array formula (Ctrl+Shift+Enter). If you just press Enter, it returns only the first value — and looks correct until you audit deeper.
Fix: Use dynamic arrays (Excel 365/2021+) — or wrap with INDEX: =INDEX(LARGE(DataRange,ROW(1:5)),ROW(1:5)).
Finally — here’s your immediate action plan. Do this before your next status meeting:
| Action | Shortcut / Location | Why It Matters |
|---|---|---|
| Audit all Names for #REF! errors | Name Manager → Filter → ‘#REF! errors’ | Catches broken links before they corrupt reports |
| Check scope on every Name used across sheets | Name Manager → select Name → view ‘Scope’ column | Prevents silent mismatches in multi-sheet models |
| Replace static ranges in Names with OFFSET/COUNTA | Edit ‘Refers To’ field in Name Manager | Makes Names resilient to row inserts/deletions |
| Find hidden Names (built-in or add-in) | Name Manager → uncheck ‘Hide built-in names’ | Reveals constants like PI(), TRUE(), and custom add-in functions |
