What Most People Miss About Finding Sheet Name Code in Excel

Why does your INDIRECT formula break when you rename a tab? Why does VBA throw ‘Subscript out of range’ even though the sheet looks fine? Why does =CELL("filename") return something completely different than what you see at the bottom of Excel?

The Problem

You’ve got a workbook with 7 sheets — some renamed, some hidden, one duplicated accidentally — and you’re trying to reference them dynamically in formulas or macros. But Excel stores two names for each sheet: the display name (what you see on the tab) and the code name (used internally by VBA). Worse: the code name doesn’t change when you rename the tab… unless you manually reset it. And if you’re using INDIRECT or GET.CELL, you’re probably referencing the wrong one.

Here’s what your workbook actually looks like behind the scenes — not what you think it looks like:

Tab Display Name	VBA Code Name	Visible?	Renamed Since Creation?	Used in INDIRECT?
Sales Q1	Sheet1	Yes	✓	✗
Budget 2024	Sheet2	Yes	✓	✗
Archive	Sheet3	No	✗	✗
Data Raw	Sheet4	Yes	✓	✓
Sales Q1	Sheet5	Yes	✓	✗
[Draft] Forecast	Sheet6	Yes	✓	✗
Summary	Sheet7	Yes	✗	✓

See the issue? Two tabs named Sales Q1. One’s Sheet1, one’s Sheet5. Your =INDIRECT("'Sales Q1'!A1") will pull from the first one — but which one is that? You can’t tell just by looking. And if Sheet1 is hidden? It still works… until it doesn’t (trust me, I learned this the hard way).

The Solution

You don’t need VBA to see the code name — though it helps to know where to look. Here’s how to find the sheet name code reliably, in order of increasing precision:

Right-click any sheet tab → “View Code” (or press Alt + F11 to open VBE, then click the sheet in Project Explorer). The (Name) property in the Properties window (press F4) is the code name. That’s Sheet1, Sheet2, etc. — unchangeable via UI.
In any cell, use this formula to list all sheet names (display + code):
=TEXTJOIN(", ",TRUE,IF(ISREF(INDIRECT("'"&ROW(INDIRECT("1:"&SHEETS()))&"'!A1")),"Sheet"&ROW(INDIRECT("1:"&SHEETS())),""))
Too messy? Use this instead in A1 of a new sheet:
=INDEX(GET.WORKBOOK(1),ROW(A1)) — then drag down. This returns full paths like [Book1.xlsx]Sheet1. Extract with =MID(A1,FIND("]",A1)+1,LEN(A1)).
For VBA users: Paste this into Immediate Window (Ctrl+G): For Each s In ThisWorkbook.Sheets: Debug.Print s.Name & " → " & s.CodeName: Next. Output appears instantly.

Once you know the code names, here’s how to safely reference them:

Goal	What Works	What Breaks
Dynamic cell reference	=INDIRECT("'"&INDEX(GET.WORKBOOK(1),2)&"'!A1")	=INDIRECT("'Sales Q1'!A1") — fails if duplicate name exists
VBA loop over sheets	For Each ws In Worksheets: … Next	Sheets("Sheet1").Range("A1") — brittle if code name changed manually
Get current sheet name	=MID(CELL("filename"),FIND("]",CELL("filename"))+1,FIND("!",CELL("filename"))-FIND("]",CELL("filename"))-1)	=SUBSTITUTE(CELL("filename"),"[","") — includes file name

Going Further

You can rename the code name — but only in VBA editor. Right-click the sheet in Project Explorer → Properties → change (Name). Set it to something meaningful like wsSalesQ1 or shRawData. Then in VBA, you can write wsSalesQ1.Range("A1").Value — no string lookup needed. It won’t break if someone renames the tab.

Surprising tip: Code names persist across saves and copies. If you copy Book1.xlsx to Book2.xlsx, Sheet1 stays Sheet1 — even if the display name changes. So macros relying on code names survive file duplication. But display names? Not guaranteed.

Need a full list in one place? Run this in a new module:

Sub ListAllSheetNames()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        Debug.Print "Display: " & ws.Name & " | Code: " & ws.CodeName
    Next ws
End Sub

It outputs to Immediate Window — perfect for auditing before sharing the file with finance or ops.

When NOT to Use This

Avoid code names in shared workbooks without VBA enabled. If recipients disable macros, any formula depending on VBA-derived names fails silently.
Don’t rely on GET.WORKBOOK() in Excel Online or Mac Excel. It’s an old XLM function — unsupported there. Use VBA or Power Query instead.
Never assume Sheet1 = first tab. Users can reorder tabs freely. Sheet1 stays Sheet1 regardless of position. Test with =SHEET(Sheet1!A1) — returns 1 even if it’s the 5th tab.

Also: if you’re using Power Query, skip all this. Just use Excel.CurrentWorkbook() — it references sheets by display name and handles duplicates gracefully.

Keyboard Shortcuts

Action	Windows Shortcut	Notes
Open VBA Editor	`Alt` + `F11`	Required to view code names
Show Properties Window	`F4`	Displays (Name) field for selected sheet
Open Immediate Window	`Ctrl` + `G`	Paste and run quick debug commands
Cycle through sheets	`Ctrl` + `Page Down` `Ctrl` + `Page Up`	Helps verify visibility vs. code name mismatch