To reference another sheet with the INDIRECT function in Excel, use a formula like =INDIRECT("'"&B1&"'!A1"), where B1 contains the sheet name. This works because INDIRECT converts a text string into a real reference, letting you build the sheet reference dynamically from cell values.
What INDIRECT Does and Why It Matters
INDIRECT is a lookup and reference function that returns a reference specified by a text string. Its main value is that the reference is not fixed at the time you write the formula—it updates when the text changes. This makes it ideal for situations where you want to switch between sheets without editing formulas manually, such as monthly reports where each month is a separate sheet (e.g., Jan, Feb, Mar). Instead of writing =Jan!B5, =Feb!B5, and so on, you put the month name in a cell and let INDIRECT build the reference.
The function has two arguments: ref_text (required) and [a1] (optional, default TRUE for A1-style references). The syntax is =INDIRECT(ref_text, [a1]). The ref_text can be a direct string like "Sheet2!A1" or a concatenation of cell values and text.
Basic Syntax and How to Write the Formula
The general pattern for referencing another sheet is:
=INDIRECT("'" & SheetNameCell & "'!" & CellAddress)
Here’s a step-by-step example. Assume you have a workbook with sheets named Jan, Feb, and Mar. In cell A1 of a summary sheet, enter the name “Jan”. In cell B1, enter the cell reference you want, like “B5”. Then in C1, write:
=INDIRECT("'"&A1&"'!"&B1)
This returns the value from Jan!B5. If you change A1 to “Feb”, the formula instantly returns Feb!B5. The single quotes around the sheet name are essential if the sheet name contains spaces or special characters (e.g., “Sales Data”). Without quotes, Excel would try to interpret the space as an operator and fail.
If you know the sheet name and cell directly, you can hardcode it: =INDIRECT("'Sales Data'!C10"). But the dynamic version is far more flexible.
Building a Drop-Down to Switch Sheets
A common use case is combining INDIRECT with a data validation drop-down. For example, create a list of sheet names (Jan, Feb, Mar) in a range, then use Data > Data Validation > List to create a drop-down in cell A1. Then in B1, use:
=INDIRECT("'"&A1&"'!B5")
Now, selecting a month from the drop-down updates the result instantly. This is a powerful way to build interactive dashboards without macros. To make it even more robust, you can use a named range for the sheet names: define a name like SheetList referring to the list, then use that name in the data validation source.
Handling Sheet Names with Spaces and Special Characters
When a sheet name contains spaces, punctuation, or starts with a number, you must enclose it in single quotes. The formula becomes:
=INDIRECT("'My Sheet'!A1")
In a dynamic version, you add the quotes manually in the concatenation:
=INDIRECT("'"&A1&"'!A1")
If you forget the quotes, Excel returns a #REF! error. Also note that sheet names are case-insensitive, so “jan” and “Jan” work the same, but the quotes are still required for spaces.
Common Errors and How to Fix Them
Three errors appear frequently:
- #REF! – The sheet name or cell reference in the text is invalid. Check for typos, missing quotes, or that the sheet exists.
- #NAME? – The function name is misspelled, or you’re using a text string that Excel can’t interpret as a reference. Ensure you’ve typed INDIRECT correctly.
- #VALUE! – The
ref_textargument is not a valid reference, often because you forgot to concatenate properly or used a cell that contains text instead of a reference.
To debug, select the cell with the formula and press F2 to see the evaluated text. Or use the Evaluate Formula tool (Formulas > Formula Auditing > Evaluate Formula) to step through the calculation. A frequent mistake is using a cell reference like “A1” in the formula but forgetting that INDIRECT treats it as text—if you want to reference a cell that itself contains a sheet name, you must concatenate correctly.
INDIRECT vs. Direct References: When to Use Each
Direct references like =Jan!B5 are static and fast. INDIRECT is volatile, meaning it recalculates every time Excel recalculates, which can slow down large workbooks. Use direct references when the sheet name won’t change. Use INDIRECT when you need dynamic switching or when you want to build references from user input. There is also a performance difference: INDIRECT is slower because Excel doesn’t track dependencies the same way. For small workbooks, this is negligible, but for thousands of formulas, it can matter.
Another alternative is the XLOOKUP or INDEX/MATCH with a helper column that lists sheet names, but those require a different structure. INDIRECT is the only function that can directly convert a text string to a reference, so it’s unique in that regard.
Using INDIRECT with Named Ranges on Another Sheet
You can also reference a named range on another sheet. If you have a defined name like SalesData that refers to a range on the Jan sheet, you can use:
=INDIRECT("SalesData")
This works because the name is global (unless you define a sheet-local name). For a sheet-local name (e.g., Jan!LocalName), you can reference it with =INDIRECT("Jan!LocalName"). Note that if you use a sheet name with spaces, you still need quotes around the sheet part, but not around the name itself: =INDIRECT("'My Sheet'!LocalName").
Frequently Asked Questions
Can I use INDIRECT to reference a closed workbook? No. INDIRECT only works with open workbooks. If the other workbook is closed, you’ll get a #REF! error. You would need to open it or use a different approach like a direct link with the full path.
Why does my INDIRECT formula return #REF! when I copy it down? This usually happens because the sheet name cell reference moves. For example, if you use =INDIRECT("'"&A1&"'!B1") and drag down, A1 becomes A2, which may be empty. Use absolute references like $A$1 if you want to lock the sheet name cell.
Is INDIRECT case-sensitive for sheet names? No, sheet names are not case-sensitive in Excel. “Jan” and “jan” refer to the same sheet. However, the text in your formula must match exactly in spelling.
Can I use INDIRECT to reference a cell in a different workbook without opening it? No, as mentioned, INDIRECT requires the workbook to be open. For closed workbooks, you’d need a direct external reference like ='[Book1.xlsx]Sheet1'!A1, which doesn’t update dynamically.
How do I reference a sheet name that contains a single quote? This is rare, but if a sheet name has an apostrophe (e.g., “O’Brien”), you need to double the quote inside the formula: =INDIRECT("'O''Brien'!A1"). The double single quote escapes the apostrophe.
Does INDIRECT work in Excel for the web or Mac? Yes, INDIRECT is available in all modern versions of Excel, including Excel for the web and Excel for Mac. The syntax is identical, but on Mac, keyboard shortcuts differ (e.g., F2 is Control+U).
Related
- Excel Group of Schools Zimbabwe: Full Guide
- Excel Gradebook Template for Teachers: How to Use
- Excel Group Lake Jackson TX: What It Is & Services
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Balance of Payments Equation: Formula and Key TermsThe balance of payments equation records a country’s transactions with the rest of the world: current account +…
- Python String Functions: Methods, Examples, and UsesPython string functions are mainly string methods—operations such as upper(), strip(), replace(), and split() that work on text.…
- Excel Date Filter Multiple Criteria: Practical MethodsTo filter dates in Excel by multiple criteria, turn on AutoFilter, open the date column’s dropdown, and choose…
- IRR in Excel: Formula, Examples, and Common ErrorsYes. In Excel for Microsoft 365 on Windows, use the IRR function to estimate the return for a…
- Excel CONCAT: Join Cell Values with a FormulaTo combine cell values in Excel, use the CONCAT function: with text in A2 and B2, enter =CONCAT(A2,B2)…
- PMT Function in SQL: Calculate Loan Payments by PeriodSQL does not have a universally available built-in PMT function. To calculate a fixed loan payment, use the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.