INDIRECT Excel Formula for a Different Sheet: Examples

If cell A1 contains a worksheet name and you want the value in B2 on that sheet, use =INDIRECT("'"&A1&"'!B2"). For example, if A1 contains North, the formula returns the value in North!B2; the sheet name can also contain spaces.

What INDIRECT does with a sheet reference

Excel normally stores a cell reference as part of a formula, such as a reference to another worksheet. INDIRECT takes a reference written as text and asks Excel to treat that text as a real reference. That makes it useful when the sheet name, cell address, or both need to come from cells rather than being fixed in the formula.

The function’s basic syntax is INDIRECT(ref_text, [a1]). The required ref_text argument is a text value that describes a valid Excel reference. The optional a1 argument tells Excel which reference style to use: TRUE or an omitted argument means A1-style references, while FALSE means R1C1-style references. Most worksheet formulas use A1 style, so the examples here leave that argument out unless stated otherwise.

In the opening example, the pieces are the worksheet name from A1, an exclamation mark that separates the sheet from the cell, and the address B2. The apostrophes around the sheet name help Excel read names that include spaces or punctuation. They are also acceptable when a simple sheet name does not strictly require them.

Build a reference from a sheet-name cell

To make a formula respond to a selected worksheet, put the exact tab name in a cell, then join that text to the address you want. For instance, a summary sheet might have North in A1, South in A2, and East in A3. A formula next to each name can retrieve the same cell from each corresponding tab. Changing the sheet name in the input cell changes which tab INDIRECT refers to.

Assume A1 contains North Region and the desired cell is B2 on that sheet. The formula is =INDIRECT("'"&A1&"'!B2"). If North Region!B2 contains 125, this formula returns 125. The apostrophe marks the beginning and end of the worksheet name within the reference text, and the exclamation mark separates that name from B2.

Sheet names must match the worksheet tabs exactly. Leading or trailing spaces in the name stored in A1, a misspelling, or a tab that has been renamed can make the text point to a nonexistent sheet. A dropdown list of valid tab names can reduce typing mistakes, but it does not make INDIRECT update the stored name automatically when someone renames a tab.

Make both the sheet and cell address dynamic

You can build the cell address from another cell as well as the sheet name. This is handy when a report needs to switch between different tabs and different target cells. Assume A2 contains North Region and B2 contains C5, with C5 being the address as text, not a value calculated from a reference. Use =INDIRECT("'"&A2&"'!"&B2). If North Region!C5 contains Ready, the result is Ready.

Keeping the sheet name and address in separate input cells makes the formula easier to inspect than typing a long reference string directly into it. It also lets you reuse the same formula pattern in multiple rows. However, both inputs must combine into a valid address. A cell address such as C5 works; a misspelled address or a reference to a deleted row or column can produce an error.

INDIRECT can also assemble a reference in R1C1 style, but the optional argument must then be FALSE. Assume A1 contains North and you want the cell in row 2, column 2 on that sheet, which is B2. Use =INDIRECT("'"&A1&"'!R2C2",FALSE). This is mainly useful when a formula needs to construct row and column numbers; for ordinary worksheet work, A1-style references are usually easier to read.

Use INDIRECT with a range or a calculation

The text can describe a range, not just one cell. That means a normal Excel function can use a range on the worksheet named in an input cell. Assume A1 contains January and cells B2:B4 on the January sheet contain 10, 20, and 30. Use =SUM(INDIRECT("'"&A1&"'!B2:B4")). The result is 60, because Excel adds 10 + 20 + 30.

This pattern can help with monthly or departmental reports that keep the same layout on separate tabs. A selector cell can hold a month or department name, while the calculation stays in place and reads the matching range. It is important that each referenced sheet use the expected layout: if one monthly tab stores the figures in a different column, the same B2:B4 reference will still point to B2:B4 and may return the wrong data without an obvious warning.

You can also use INDIRECT inside other functions, including AVERAGE, COUNT, and lookup formulas. The function does not decide which data is appropriate; it only turns the text into a reference. Check that the chosen sheet and range contain the intended data, especially when the result feeds a larger report.

Sheet names, apostrophes, and safe inputs

Excel sheet names can contain spaces, so a reference text that works for a tab named Sales may fail for a tab named Sales 2026 if the name is not quoted correctly. Using apostrophes around the name, as in the examples, is a practical habit that handles spaces consistently. The apostrophes are part of the text assembled for the reference, not part of the worksheet tab name itself.

A less common complication is an apostrophe inside the sheet name itself. In reference syntax, an apostrophe within a quoted sheet name must be doubled. If A1 contains O’Brien, the formula needs to convert that single apostrophe into two for the assembled reference. Assume A1 contains O’Brien and the target is B2. Use =INDIRECT("'"&SUBSTITUTE(A1,"'","''")&"'!B2"). The SUBSTITUTE function performs that doubling before INDIRECT interprets the reference.

Because INDIRECT evaluates text as a reference, do not treat arbitrary text inputs as harmless labels. Keep the allowed sheet names and addresses controlled, for example through a maintained list or a clearly labeled input area. This is primarily a reliability concern: an unexpected value can create an invalid reference or direct a calculation to an unintended location.

Common errors and how to fix them

The most common result of a broken INDIRECT reference is #REF!, which means Excel cannot resolve the text into a valid reference. Check the exact tab spelling, the cell address, the exclamation mark, and the placement of apostrophes. Also check whether the referenced sheet or cell was deleted. If the formula is assembled from multiple cells, inspect the contents of each input rather than looking only at the final formula.

Use these checks when a formula does not return the expected value:

Compare the sheet-name text with the tab name, including spaces and punctuation.
Confirm that the address text is valid and refers to the intended cell or range.
Make sure the formula joins the sheet and address with an exclamation mark.
Quote the worksheet name with apostrophes, particularly when it contains spaces.
For a sheet name containing an apostrophe, double that character in the reference text, such as with SUBSTITUTE.
If a workbook name is part of the reference, confirm that the external workbook is open.

A formula can return a valid but unexpected value if the selected worksheet has the right name but a different layout. For example, if one tab has totals in B2 and another has a heading in B2, a formula that always requests B2 will return different kinds of content. Standardizing the layout across tabs is often as important as getting the formula syntax right.

External workbooks and limitations

INDIRECT can construct references to another workbook, but external references have an important limitation: the workbook being referenced generally must be open for INDIRECT to resolve the reference. When that source workbook is closed, the formula can return #REF!. This makes INDIRECT a poor choice for links that must continue working while source workbooks are closed or moved.

External references also depend on workbook and worksheet names being accurate. If a file is renamed, moved, or unavailable, a text-built reference may no longer work as intended. For a small workbook that is always open alongside the source, the approach may be acceptable. For a durable link to data in another file, consider whether a direct external reference or an import workflow better fits the task.

INDIRECT is a volatile function: Excel recalculates it when recalculation occurs, even when the cells it refers to have not changed. A few uses in a modest workbook are usually straightforward, but thousands of volatile formulas can add calculation work and make a large workbook slower. Use it where dynamic reference construction is genuinely valuable rather than as a default replacement for ordinary references.

INDIRECT versus a direct sheet reference

A direct reference is simpler when the worksheet is fixed. Assume the sheet is named North Region and the required value is in B2. Use ='North Region'!B2. If B2 contains 125, this formula returns 125. It does not need INDIRECT, and Excel can generally adjust direct references when a worksheet is renamed within the workbook.

The trade-off is that a direct reference does not switch to a different sheet just because a selector cell changes. INDIRECT is appropriate when a user-selected tab name must determine the target dynamically. Direct references are usually preferable when the target is known, because they are easier to audit, do not rely on text being assembled correctly, and avoid INDIRECT’s volatility.

Another important difference is that a text-built reference is not the same as a normal reference in Excel’s dependency tracking. Because INDIRECT hides the target inside text, some workbook tools and formula auditing can be less informative about which cells depend on which other cells. If formulas will be maintained by several people, clarity and ease of auditing may matter more than the convenience of switching tabs.

Frequently Asked Questions

Can INDIRECT refer to a different sheet in the same workbook?
Yes. Build text containing the worksheet name, an exclamation mark, and the target cell or range, then pass that text to INDIRECT. Quoting the sheet name with apostrophes is a reliable approach, especially when the name contains spaces.

Do I need apostrophes around a sheet name?
They are not strictly necessary for every simple sheet name, but they are useful and safe to include. They allow names with spaces to be represented correctly. If the name itself contains an apostrophe, double that character inside the quoted reference text.

Why does INDIRECT return #REF! after I rename a sheet?
If the formula gets its sheet name from text in a cell, renaming the tab does not necessarily change that text. Update the input cell to match the new tab name. A direct worksheet reference behaves differently because Excel can update its reference when a sheet is renamed.

Can INDIRECT reference a closed Excel workbook?
In general, no: an external workbook referenced through INDIRECT must be open. If it is closed, the function can return #REF!. Consider a direct external link or another data-import method if the source file needs to remain closed.

Is INDIRECT better than a direct reference?
Not usually when the worksheet is fixed. A direct reference is clearer and avoids INDIRECT’s volatility. Choose INDIRECT when the reference really must change based on text, such as a sheet name selected by the user.

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.