Yes. In Excel, INDIRECT can return a reference to a cell on another sheet when the sheet and cell reference are written as text. Assume a sheet named January has 1250 in B2; on another sheet, enter =INDIRECT("'January'!B2") to return 1250.
What INDIRECT does across sheets
Excel normally lets you refer directly to a cell on another worksheet. A reference such as January!B2 points to cell B2 on the January sheet. INDIRECT does something different: it takes text that looks like a reference and interprets that text as an actual reference. That distinction matters when the sheet name or cell address comes from a cell, or when you want to assemble a reference from several pieces.
The function does not copy a value or move data between sheets. It evaluates the reference text and returns the value found at the referenced location. This makes it useful for reports that select a source sheet from a list, but it also means that the text must form a valid reference in the current workbook.
A direct reference is usually simpler if the source sheet is fixed. INDIRECT is most helpful when the reference needs to change based on a selection, a label, or another input. It can also make a workbook harder to audit, because the final destination may not be visible as a normal reference in the formula.
Basic syntax and a fixed sheet name
INDIRECT accepts reference text as its first argument. Its optional second argument specifies whether that text uses A1-style or R1C1-style references; if you leave it out, Excel uses A1-style references. In ordinary worksheet formulas, A1 style is the familiar notation with column letters and row numbers.
For the opening example, assume the January worksheet exists and contains 1250 in B2. Enter this on the sheet where you want the result:
=INDIRECT("'January'!B2")
The returned value is 1250. The text inside the quotation marks is the reference Excel evaluates. The apostrophes around January are part of that text; they identify the sheet name. A worksheet name without spaces can often be referenced without apostrophes, but quoting sheet names consistently is a practical habit because it also works with names that contain spaces or punctuation.
The formula is still a formula in the destination cell, not text. If you omit the outer double quotation marks, Excel will not receive the reference text in the required form. If you type the formula into a cell formatted as Text, Excel may display the characters instead of calculating them; change the cell format to General and re-enter the formula if that happens.
Sheet names with spaces and apostrophes
A sheet name containing spaces needs apostrophes around it in a worksheet reference. Suppose a sheet is named Sales Data and its C5 cell contains 840. With that layout, use the following formula on the destination sheet to return 840:
=INDIRECT("'Sales Data'!C5")
Sheet names may also contain an apostrophe. In a reference, Excel represents an apostrophe inside the name by doubling it. For example, if the worksheet is named Director’s Report and D4 contains 72, the reference text must use two apostrophes inside the sheet name. Assuming that layout, this formula returns 72:
=INDIRECT("'Director''s Report'!D4")
Those doubled apostrophes are not a typo: they are the escaping convention for a sheet name that contains an apostrophe. If you are creating the text dynamically, the construction can be less obvious, so test it with the exact worksheet name rather than assuming that punctuation will be handled automatically.
Build the sheet reference from a cell
A common use is to let a user choose a worksheet name in a cell. Assume A2 on the current sheet contains January, and B2 on the January sheet contains 1250. The formula below joins the sheet name from A2 to the fixed cell address B2; it returns 1250:
=INDIRECT("'"&A2&"'!B2")
The ampersands join text fragments. The first fragment supplies an apostrophe, A2 supplies the sheet name, and the remaining fragment supplies the closing apostrophe, an exclamation mark, and the cell address. The exclamation mark separates a worksheet name from its cell reference. When A2 changes to another existing sheet name, the formula points to B2 on that sheet instead.
You can make the cell address dynamic too. Assume A2 contains January, B1 contains the text B2, and the January sheet’s B2 cell contains 1250. This formula returns 1250 by combining the selected sheet name with the address in B1:
=INDIRECT("'"&A2&"'!"&B1)
Here B1 must contain a valid address as text, such as B2, not a value that merely happens to be stored in cell B2. This approach is flexible, but it raises the chance of a typo: a misspelled sheet name or invalid address will not be corrected automatically. A dropdown list of valid sheet names can reduce mistakes, although INDIRECT itself does not create or validate that list.
Reference a range or calculate across a selected sheet
INDIRECT can create a range reference as well as a single-cell reference. That lets another function work on a range chosen by sheet name. Assume A2 contains January, and January!B2:B7 contains 100, 200, 300, 400, 500, and 600. On the destination sheet, this formula returns 2100:
=SUM(INDIRECT("'"&A2&"'!B2:B7"))
The sum is 2100 because those six values add to 2100. If A2 contains the name of a different sheet with values in B2:B7, the formula sums that sheet’s corresponding range instead. The range endpoints are fixed in this example; they could also be assembled from text, but doing so makes the formula more difficult to check.
For a monthly report, for instance, a cell could hold the selected month and a formula could retrieve that month’s total from a consistent cell or sum a consistent range. The workbook design must keep the relevant cells in the same positions on each monthly sheet. If one month puts totals in B2 and another puts them in C2, a formula that always points to B2 will not adapt to that structural difference.
Common errors and how to fix them
The most frequent problem is #REF!, which means Excel cannot resolve the text as a valid reference. Check that the sheet exists, that its name is spelled exactly as shown on the tab, that the cell or range address is valid, and that the punctuation around a sheet name is correct. Renaming or deleting a sheet can also leave text-based references pointing to a name that no longer exists; unlike a conventional direct reference, a text string in INDIRECT may not update when a sheet is renamed.
- Check the exact text: Look for leading or trailing spaces in a cell that supplies the sheet name. A value that appears to say January may include an invisible space and fail to match the tab name.
- Check the quotation marks: The reference text needs double quotes around the text expression in the formula, and apostrophes around sheet names are a safe choice. A sheet name containing an apostrophe needs the doubled-apostrophe form shown above.
- Check the address: A text value such as B2 is a valid cell address, but a blank value or an incomplete address is not a usable reference.
- Check workbook names: External references constructed with INDIRECT have an important limitation: the source workbook must be open. If it is closed, INDIRECT returns
#REF!for that external reference.
Other errors may come from the surrounding calculation rather than from INDIRECT itself. For example, SUM can return a result based on the cells it receives, while a reference text that points to the wrong but valid range can produce a plausible yet incorrect total. Verify the destination by temporarily checking the sheet name and address inputs, and compare the result with a known value or a direct reference.
Performance, recalculation, and workbook maintenance
INDIRECT is a volatile function. Excel recalculates volatile formulas more often than formulas that depend only on their direct precedents, including during recalculation triggered by changes elsewhere in the workbook. A handful of INDIRECT formulas is often manageable, but a large workbook with thousands of them may calculate more slowly, especially when each formula constructs a different reference.
There is also a maintenance trade-off. The formula can follow a selection stored in a cell, but Excel sees the target as text rather than a conventional dependency. That can make it harder to trace precedents, use formula auditing, or understand the effect of renaming a sheet. Keep the sheet-name inputs clear, document expected names, and avoid using INDIRECT simply to make a fixed reference look more flexible.
INDIRECT does not bypass workbook protection or grant access to a sheet a user cannot otherwise access. It is a reference-building function, not a security feature. Do not treat a hidden sheet or a complicated formula as protection for confidential information.
When a direct reference or another approach is better
If the source sheet never changes, use a direct reference. Assuming January!B2 contains 1250, this direct formula returns 1250 and is easier to read and maintain:
=January!B2
For a sheet name with spaces, use apostrophes in the direct reference. Assuming Sales Data!C5 contains 840, this formula returns 840:
='Sales Data'!C5
Direct references are generally preferable when you know the target in advance: they are easier to inspect and do not have INDIRECT’s volatile recalculation behavior. If you need to choose among a fixed set of sheets, compare the workbook’s layout and maintenance requirements before adopting INDIRECT. A well-organized data table, with a month or category recorded in a column, can often be filtered or summarized without building references to separate worksheets at all.
INDIRECT is appropriate when the requirement really is to assemble a reference from text—for example, when users select a worksheet and every worksheet follows the same layout. It is less suitable when source workbooks may be closed, sheet names are frequently changed, or formulas must be easy for others to audit. There is no universal advantage to using it: its value is the flexibility to choose a reference dynamically, balanced against those limitations.
Frequently Asked Questions
Can INDIRECT refer to another sheet in the same workbook?
Yes. Put the sheet name, an exclamation mark, and the cell or range address in the reference text. For a fixed sheet, a reference such as =INDIRECT("'January'!B2") returns the value in January!B2, provided that sheet and cell exist.
Do sheet names with spaces need apostrophes?
Yes, include apostrophes around a sheet name that contains spaces in the reference text. It is also sensible to quote simple sheet names consistently. If the name itself contains an apostrophe, double that apostrophe inside the reference, as in the Director’s Report example.
Can INDIRECT use a sheet name stored in a cell?
Yes. Join the cell containing the name to the rest of the reference text. The name must match an existing sheet, including spaces and punctuation. If it does not, the formula will usually return #REF!.
Why does INDIRECT return #REF! for another workbook?
INDIRECT cannot resolve a reference to a closed external workbook. Open the source workbook for the external reference to work, or use a different method if the source must remain closed.
Does INDIRECT update when I rename a sheet?
Not reliably when the sheet name is embedded in text or supplied as text. Excel can update ordinary direct references when a sheet is renamed, but a text string used by INDIRECT may continue to contain the old name and then return #REF!. Update the text or the cell that supplies the name.
Is INDIRECT better than a direct sheet reference?
Only when the reference needs to be chosen or assembled dynamically. For a fixed target, a direct reference is clearer and avoids INDIRECT’s volatility and text-based maintenance issues.
Related
- Excel Employee Work Schedule: Setup and Tips
- Excel EIN Number Format: Keep All 9 Digits Visible
- Excel Dependent Drop-Down Lists: Setup and Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel LET Function: Syntax, Examples, and Common ErrorsExcel’s LET function lets you give names to values or calculations inside a formula, then use those names…
- Excel Days Until Formula: Calculate a CountdownTo calculate calendar days until a date in Excel, put the target date in A2 and enter =A2-TODAY()…
- Excel Filter by Partial Text: How to Find MatchesTo filter Excel rows by text found anywhere in a cell, select a cell in your data, choose…
- Match Excel Two Sheets: Formulas and MethodsYes. To match two Excel sheets, compare a shared key such as an ID or email address. If…
- Excel Formula for Time Between DatesTo find the time between two dates in Excel, subtract the earlier date or date-time from the later…
- Excel Day of Week from Date: Formulas and FormattingIf your date is in A2, enter =TEXT(A2,"dddd") to show its full weekday name; for example, an actual…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.