Excel INDIRECT Function: Reference Another Sheet

Formulas & functions · Updated November 18, 2026

Yes. In Excel, INDIRECT can turn a text string containing a worksheet reference into a usable reference. For example, if Sheet2 contains 125 in B3, =INDIRECT("Sheet2!B3") returns 125; if a sheet name is stored in a cell, you can build the reference from that cell instead.

What INDIRECT does

INDIRECT is a reference function: it interprets text as a cell or range address. The text can describe a cell on the current worksheet, a cell on another worksheet in the same workbook, or—in some circumstances—a reference to another workbook. The function does not search for a sheet or copy its data. It constructs a reference at calculation time, and Excel evaluates that reference where the formula is used.

This makes INDIRECT useful when the worksheet name or address should change based on a selection. A normal formula such as =Sheet2!B3 points directly to one location. An INDIRECT formula can instead assemble the sheet and cell address from text in cells, so changing a selector can redirect the result without rewriting the formula.

Reference another sheet with a fixed name

For a sheet with a simple name and a fixed cell, put the sheet name, an exclamation mark, and the cell address inside a text string. Assume Sheet2 has the number 125 in B3 and the formula is entered on a different sheet. This formula returns 125:

=INDIRECT("Sheet2!B3")

The exclamation mark separates the worksheet name from the cell address. You can also pass a range reference and use it inside another function. Assume Sheet2 has values 10, 15, and 20 in B2:B4. This formula returns 45:

=SUM(INDIRECT("Sheet2!B2:B4"))

That example illustrates an important distinction: INDIRECT supplies the reference, while SUM performs the addition. The function is not limited to returning one cell, but the consuming function must be able to use the reference or range you provide.

Use quotation marks for sheet names with spaces

Worksheet names containing spaces or certain punctuation need single quotation marks around the sheet name in the reference string. For example, a sheet named Sales West is referred to as 'Sales West'!B3. Assume Sales West contains 125 in B3; the formula below returns 125:

=INDIRECT("'Sales West'!B3")

It is often sensible to include those single quotation marks even when a sheet name does not require them. They make the reference format clear and allow the formula to keep working if a simple sheet name is later changed to one containing spaces. The single quotation marks are part of the reference text; the double quotation marks delimit the text string in the Excel formula.

Build the sheet reference from a cell

A common setup stores the worksheet name in a selector cell. Assume A1 contains the exact tab name Sales West, that worksheet has 125 in B3, and the formula is entered on another sheet. The formula joins the single quotation marks, the value in A1, and the fixed cell address; it returns 125:

=INDIRECT("'"&A1&"'!B3")

The ampersand joins pieces of text. If A1 changes to another existing tab name, the formula points to B3 on that tab instead. The sheet name must match the tab exactly, including spaces. A dropdown list of valid sheet names can help prevent typing mistakes, although INDIRECT itself does not create or check that list.

You can make both the sheet and the cell address selectable. Assume A1 contains Sales West, B1 contains D8, and D8 on Sales West contains 42. The formula below returns 42:

=INDIRECT("'"&A1&"'!"&B1)

Here, A1 supplies the worksheet name and B1 supplies the address. Treat an address in a cell as text in the expected format, such as D8 or B2:B10. If either piece is blank, misspelled, or not a valid reference, the assembled reference may fail.

Choose between INDIRECT and a direct reference

Use the simplest reference that meets the need. A direct worksheet reference is usually clearer when the source tab and cell are fixed. INDIRECT earns its extra complexity when the reference genuinely needs to vary—for example, a monthly report where a selected month determines which monthly tab supplies a value.

  • Fixed source: Prefer an ordinary sheet reference when the source will not change. It is easier to read and audit.
  • Variable sheet: Consider INDIRECT when a cell selection should choose among existing tabs that share a consistent layout.
  • Many sheets to combine: If the goal is to consolidate data from numerous tabs, a designed data table, Power Query, or another structured approach may be easier to maintain than a large collection of text-built references.
  • Look up a value by a key: If the real task is matching an ID or name in a data table, lookup functions such as XLOOKUP may fit better than constructing a sheet address. XLOOKUP is available in Microsoft 365 and Excel 2021 or later.

INDIRECT is not automatically more flexible in every sense. It is flexible about which address it points to, but the text has to be assembled accurately, and Excel cannot treat that text in the same way it handles a conventional reference when tracking changes.

Common errors and reference changes

The most common failure is #REF!, which means Excel cannot resolve the text as a valid reference. Check the tab spelling, the exclamation mark, the cell address, and any required quotation marks. Also confirm that a selector cell does not contain an unintended leading or trailing space. A reference to a worksheet that has been deleted or renamed can no longer resolve if the formula still builds the old name.

INDIRECT uses text rather than a normal linked reference. If a formula directly refers to a cell and rows are inserted, Excel can often adjust that direct reference. Text inside INDIRECT is not automatically rewritten just because the referenced rows or columns are rearranged. If a formula builds a reference to B3 and a change means the intended data is now in B4, the text may continue to point to B3. Review these formulas when changing a workbook’s layout.

Another limitation concerns external workbooks. INDIRECT can use a reference to a different workbook only while that source workbook is open. If the referenced workbook is closed, INDIRECT returns #REF!. For links that must work with closed files, use a different approach rather than relying on INDIRECT. External links also need accurate workbook and worksheet names, so they can be fragile when files are moved or renamed.

If a tab name itself contains an apostrophe, that apostrophe needs special handling in the reference text. For example, a name such as O’Brien must represent the apostrophe as two apostrophes in the worksheet reference. For complicated or user-entered tab names, avoiding punctuation in names or using a carefully tested name-building method can reduce errors.

Calculation speed and workbook maintenance

INDIRECT is a volatile function. Volatile formulas recalculate whenever Excel recalculates, even if a change does not appear to affect their particular input cells. A few INDIRECT formulas in a modest workbook are often manageable, but many thousands of them can contribute to slower recalculation, especially when each formula is combined with other expensive calculations.

To keep a workbook maintainable, use a consistent layout on the tabs that INDIRECT may select, keep the set of valid sheet names controlled, and document what the selector cells contain. Avoid copying a text-built reference across a large grid unless the changing row and column logic is deliberate. If recalculation becomes slow, test whether replacing some INDIRECT formulas with direct references, a lookup, or a consolidated data source is practical.

Frequently Asked Questions

Can INDIRECT reference a cell on another sheet?
Yes. Put a valid worksheet reference in a text string, such as "Sheet2!B3", as the argument to INDIRECT. Use single quotation marks around the sheet name in the reference text when it contains spaces or punctuation.

How do I make the sheet name dynamic?
Store the exact worksheet tab name in a cell, then join that cell’s value with the target address and the worksheet separator. The tab must exist, and its name must match the text supplied by the cell.

Why does my INDIRECT formula return #REF!?
Check for a misspelled or renamed sheet, an invalid cell address, missing quotation marks, or extra spaces in the text. If the formula points to another workbook, make sure that workbook is open; INDIRECT cannot resolve an external reference to a closed workbook.

Does INDIRECT update when I rename a worksheet?
Not reliably when the worksheet name is embedded as text or constructed from a cell. Excel may update ordinary direct references during a rename, but text inside INDIRECT is not a normal reference that Excel can rewrite. Update the text or selector value to the new tab name.

Is INDIRECT better than a direct reference?
Usually not when the target is fixed. A direct reference is simpler and easier for Excel to track. INDIRECT is useful when the source sheet or address must change based on text, but it adds maintenance and recalculation considerations.

Can INDIRECT refer to a closed external workbook?
No. INDIRECT returns #REF! when its external workbook reference points to a closed file. Use a different linking or data-import method if the source workbook needs to remain closed.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.