Excel INDIRECT Across Sheets: How to Reference Other Tabs

Formulas & functions · Updated November 20, 2026

Yes. Excel’s INDIRECT function can refer to a cell or range on another sheet when you build that sheet reference as text. For example, if A2 contains the sheet name East, =INDIRECT("'"&A2&"'!B2") returns the value in East!B2.

This is useful when a formula needs to switch sheets based on a selector cell, but it is not necessary for ordinary cross-sheet references. A direct reference such as =East!B2 is simpler when the sheet is fixed. The key difference is that INDIRECT turns text into a reference at calculation time.

How INDIRECT references another sheet

INDIRECT takes a text string that looks like a cell or range reference and interprets it as an actual reference. Its syntax is INDIRECT(ref_text, [a1]). The first argument is required; the optional second argument tells Excel whether the text uses A1-style or R1C1-style notation. If you leave it out, Excel assumes A1 notation.

For a fixed sheet, you can put the full reference inside the text argument. With the value 125 in B2 on a sheet named January, enter =INDIRECT("'January'!B2") in any other sheet to return 125. The single quotation marks around January are part of the reference text. They are safe to use even when a sheet name has no spaces.

To make the sheet name changeable, store a sheet name in a cell and join it to the rest of the reference. In the opening example, A2 supplies the name and B2 is the cell to retrieve. If A2 contains East and East!B2 contains 125, the formula returns 125. If A2 changes to West, the same formula refers to West!B2 instead.

Build a sheet selector step by step

A selector is handy for a summary page that displays a result from one of several similarly structured sheets. For example, a workbook might have sheets named East, West, and Central, each with the same layout. Put a valid sheet name in a selector cell, then use INDIRECT to fetch the same-position value from whichever sheet is selected.

  1. Create or identify the source sheets, and keep the data you want to retrieve in the same cell on each sheet.
  2. Enter one of the exact sheet names in a selector cell, such as A2 on a Summary sheet.
  3. In the result cell, build the reference by joining the sheet name, an exclamation mark, and the cell address.
  4. Change A2 to another existing sheet name to switch the result.

Assume A2 on Summary contains East and East!B2 contains 125. Enter =INDIRECT("'"&A2&"'!B2") in the result cell; it returns 125. The apostrophes delimit the sheet name, while the exclamation mark separates the sheet name from the cell address. If A2 contains a name that does not match a sheet, the formula returns an error rather than creating or finding a sheet automatically.

You can also let a second cell provide the address. Assume A2 contains East, B1 contains the text C5, and East!C5 contains Ready. The formula =INDIRECT("'"&$A$2&"'!"&B$1) returns Ready. The dollar signs keep the selector row or column fixed if you copy the formula. Use this added flexibility only when you need it: a hard-coded address is easier to audit.

Use INDIRECT with a range on another sheet

INDIRECT can point to a range as well as one cell. This lets a formula such as SUM use a range on the selected sheet. Suppose A2 on Summary contains East, and East!B2:B4 contains 120, 80, and 50. With that layout, =SUM(INDIRECT("'"&A2&"'!B2:B4")) returns 250. If A2 changes to West, Excel sums West!B2:B4 instead, provided that sheet exists.

The same approach can be used inside other functions that accept a reference or range. For example, a count or average can use the selected sheet’s corresponding range. The important part is that the text assembled inside INDIRECT must describe a valid reference, and the range must make sense for the surrounding function. INDIRECT does not check that different sheets have identical layouts; it simply follows the address you give it.

For a formula copied down a report, decide which parts should move. A reference such as B2:B4 changes when copied in the usual way if it is part of a direct formula, but text inside a quoted string does not. If you need the address itself to vary, construct that part of the text deliberately or use a simpler direct reference. Building elaborate text strings can make a workbook harder to maintain.

Sheet names, spaces, and apostrophes

Sheet names containing spaces or punctuation need quotation marks in a conventional worksheet reference. For example, a direct reference to cell B2 on a sheet called North Region is ='North Region'!B2. In INDIRECT, the quotation marks around the sheet name belong inside the text string, as in =INDIRECT("'North Region'!B2"). Including the single quotes consistently avoids problems when a sheet name later changes to include a space.

If the sheet name comes from a cell, ordinary concatenation works for names without apostrophes. A sheet name that itself contains an apostrophe needs that apostrophe doubled inside the reference text. For a selector cell A2 containing the sheet name, a safer construction is =INDIRECT("'"&SUBSTITUTE(A2,"'","''")&"'!B2"). If A2 contains O’Brien, this builds the quoted reference Excel expects for that sheet.

Sheet names are not case-sensitive for normal reference matching, but spelling and spaces must match. Extra spaces in the selector cell can therefore cause a failure. If the name is being typed manually, a drop-down list can reduce spelling mistakes; however, the selector still needs to contain the actual worksheet name, not a descriptive label that differs from it.

Common errors and ways to diagnose them

The most common problem is #REF!, which means the text does not resolve to a valid reference. Check that the sheet exists, the name is spelled correctly, the exclamation mark is present, and the cell or range address is valid. Also check for spaces before or after a name in the selector cell. A blank selector usually creates an invalid reference too.

A formula may also return the wrong value without an error if the reference is valid but points to the wrong place. Confirm the selected sheet and address separately: temporarily type the completed reference as a normal formula, such as ='East'!B2, and compare the result. This can help distinguish a bad sheet name from a mistaken cell address.

You can display a more readable message when a reference fails. Assume A2 on Summary should contain an existing sheet name, and the formula is intended to return that sheet’s B2 value. Enter =IFERROR(INDIRECT("'"&A2&"'!B2"),"Check sheet name"); if the reference is invalid, the result is Check sheet name. Use this cautiously: IFERROR catches errors other than a missing sheet as well, so the message may hide a different problem in a larger formula.

Another source of confusion is expecting INDIRECT to read a formula written as text in a cell. INDIRECT interprets reference text, such as East!B2; it does not evaluate arbitrary Excel formula text stored in a cell. It also cannot use a sheet name supplied by a worksheet formula to create a new tab. The target sheet and target address must already be valid.

Performance and workbook limitations

INDIRECT is a volatile function. Volatile formulas recalculate whenever Excel recalculates, even when their apparent inputs have not changed. A few INDIRECT formulas are unlikely to matter in a small workbook, but a large model with many such formulas can recalculate more often and may feel slower. Keep the number of INDIRECT formulas under control in workbooks with extensive data or complex calculations.

INDIRECT also makes references less visible to Excel’s dependency tracking than direct references. A direct formula such as =East!B2 clearly identifies its source. A formula that constructs a sheet name from text is more difficult to trace, and Excel’s auditing tools may not show the relationship as clearly. This matters when someone else needs to review, update, or troubleshoot the workbook.

For external workbooks, INDIRECT has an important limitation: a reference to another workbook generally requires that workbook to be open. If the source workbook is closed, INDIRECT can return #REF!. This differs from many ordinary external links, which can often retain values from a closed source workbook. If you need reliable links to closed files, test the exact setup in your Excel version and consider a direct external reference or a data-import approach instead.

INDIRECT is available in current desktop Excel, including Microsoft 365 for Windows and Mac. Excel for the web supports many formulas, but web editing and calculation can differ for some workbook features; verify the behavior in the browser if the workbook depends on external links or complex references. The core sheet-reference examples above use standard worksheet formulas, not VBA or a special add-in.

When to choose a direct reference or another approach

Use INDIRECT when the sheet name genuinely needs to be selected or assembled dynamically. Typical examples include a report selector, a workbook with identical monthly tabs, or a template where a user chooses a source sheet. It is especially useful when the formula’s target must change based on a cell value and the source sheets follow a consistent layout.

Prefer a direct reference when the sheet is known and fixed. For a single value on East, =East!B2 (or ='North Region'!B2 for a name with a space) is more readable, easier to trace, and not volatile. For a workbook that stores records in one structured table, filtering or looking up rows from that table is often simpler and more robust than creating a separate sheet for each category or month.

For a recurring monthly process, consider whether the workbook design can avoid switching between tabs altogether. A single table with a Month column can often support summaries with standard functions or PivotTables, and adding a new month then means adding rows rather than changing formulas. INDIRECT can solve a real sheet-selection problem, but it does not remove the maintenance burden of keeping many worksheets aligned.

Frequently Asked Questions

Can INDIRECT refer to another sheet in the same workbook?
Yes. Put the sheet name and cell or range address together as reference text, such as =INDIRECT("'January'!B2"). The named sheet must exist, and the reference must be valid.

Do I need single quotes around a sheet name?
They are required for sheet names with spaces or certain special characters. You can use them around simple names too, which keeps the formula consistent. In a dynamically assembled name, include the quotes as text in the formula.

Can INDIRECT use a sheet name stored in a cell?
Yes. If A2 contains East, =INDIRECT("'"&A2&"'!B2") refers to East!B2. A2 must match an existing worksheet name.

Why does INDIRECT return #REF!?
Usually the assembled reference is invalid: the sheet name may be missing or misspelled, the address may be wrong, or the target sheet may not exist. For external workbook references, a closed source workbook can also be the cause.

Is INDIRECT better than a normal sheet reference?
Not for a fixed target. A direct reference is easier to read and generally more efficient. INDIRECT is useful when the target sheet needs to change based on a selector or other text.

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.