Excel Formula to Reference a Sheet Name from a Cell

To reference a worksheet named in a cell, use Excel’s INDIRECT function. If A1 contains the sheet name and you want the value from B2 on that sheet, enter =INDIRECT("'"&A1&"'!B2"). The formula turns the sheet name and cell address into a reference that Excel can evaluate.

How a cell-based sheet reference works

A normal worksheet reference is written directly into a formula, such as a reference to cell B2 on a sheet named Sales. That works well when the sheet is known in advance, but it does not let a formula use the text in a cell as the sheet name by itself. Excel treats that text as text, not as part of a reference.

INDIRECT bridges the gap: it interprets a text string as a cell reference. In the example above, A1 supplies the sheet name, the exclamation mark separates the sheet name from the cell address, and B2 identifies the cell to retrieve. If A1 contains Sales, Excel evaluates the assembled text as a reference to cell B2 on the Sales worksheet.

This is useful in summary sheets, templates, and reports where a user selects or types a worksheet name. The formula returns the value from the referenced cell; it does not return the address as text. It also does not create a new worksheet or verify the name before trying to use it.

Build the reference correctly

The single quotation marks around the sheet name are important. Excel accepts quoted sheet names whether or not they contain spaces, and the quotes prevent a name such as Quarterly Results from being misread. The exclamation mark belongs after the closing quote. The basic pattern places the sheet-name cell before the fixed address.

For a sheet named Quarterly Results, put that exact name in A1. With the sheet name in A1 and the desired cell fixed at B2, use =INDIRECT("'"&A1&"'!B2"). The formula returns the current value in B2 on Quarterly Results. If that cell contains 1250, the result is 1250.

Sheet names can contain an apostrophe, as in O’Brien. In a worksheet reference, an apostrophe inside the name must be doubled. Use SUBSTITUTE to do that automatically rather than relying on the cell value to be formatted already.

With the sheet name in A1 and the target cell fixed at B2, use =INDIRECT("'"&SUBSTITUTE(A1,"'","''")&"'!B2"). If A1 contains O’Brien, the formula constructs the correctly escaped reference and returns the value from B2 on that sheet. The two apostrophes in the generated reference represent one apostrophe in the actual sheet name.

Use a cell to choose both the sheet and address

You can make the cell address dynamic as well as the worksheet name. For example, enter a sheet name in A1 and an address such as B2 in B1. The formula can then assemble both pieces. Keep the address in B1 as plain text; it should not include an equals sign or the sheet name.

With the sheet name in A1 and a cell address such as B2 stored as text in B1, enter =INDIRECT("'"&SUBSTITUTE($A$1,"'","''")&"'!"&$B$1). If A1 contains O’Brien and B1 contains B2, Excel returns the value of O’Brien!B2. The dollar signs keep the input cells fixed if you copy the formula elsewhere.

This approach can be handy when a report lets a user choose which measure to display. It also means the address in B1 must be valid. A typo such as B22 may produce a valid reference to the wrong cell, while an invalid address produces an error. For a fixed target cell, leaving the address directly in the formula is usually simpler and less prone to mistakes.

Sum a range on the selected worksheet

The text assembled for INDIRECT can identify a range, not just one cell. Put the worksheet name in A1 and wrap the resulting reference in a function such as SUM. This is useful for comparing totals across similarly structured sheets, provided each sheet uses the same layout.

With the sheet name in A1 and the values to total in B2:B20 on every relevant sheet, use =SUM(INDIRECT("'"&SUBSTITUTE(A1,"'","''")&"'!B2:B20")). If the cells on the selected worksheet add up to 840, the formula returns 840. It does not add the same range across every worksheet; it sums the range on the one sheet named in A1.

For many monthly sheets, for example, this can provide a compact way to select one month’s total. The sheets need consistent cell locations for the result to be meaningful. If one month’s numbers are in a different column, the formula may still calculate but return an incorrect figure.

Copying formulas and choosing inputs

Whether references move when copied depends on the usual Excel reference rules. In a formula using A1, copying one column to the right normally changes that input reference to B1. Use dollar signs when you want the input cell to stay put. In the dynamic-address example, both input references are absolute, so the formula can be copied without shifting them.

For a selector, use Data Validation to reduce typing errors: select the input cell, then choose Data > Data Validation and set Allow to List. A list of valid sheet names can be maintained in worksheet cells and used as the list source. This helps users pick an existing name, but it does not make a renamed or deleted worksheet reference update automatically if the list itself is stale.

Excel sheet names are limited to 31 characters and cannot contain certain characters, including a colon, slash, question mark, asterisk, or square brackets. Spaces and many punctuation marks are allowed. The name in the selector must match the worksheet tab name; leading or trailing spaces in the input can therefore cause a reference to fail. Sheet-name matching is not case-sensitive.

Common errors and troubleshooting

If the referenced sheet does not exist, the address is malformed, or the text in the selector has an accidental space, INDIRECT generally returns #REF!. Check the tab name and the constructed address first. A quick way to narrow down the problem is to compare the text in the selector with the worksheet tab and confirm that the referenced cell or range exists.

To display a more helpful message instead of an error, wrap the reference in IFERROR. With the sheet name in A1 and the desired cell fixed at B2, use =IFERROR(INDIRECT("'"&SUBSTITUTE(A1,"'","''")&"'!B2"),"Check sheet name"). If the reference cannot be resolved, the cell displays Check sheet name. This can make a report easier to read, but it can also conceal the underlying cause, so use a specific message and keep the input easy to inspect.

If you see an unexpected result rather than an error, verify that the formula points to the intended cell on the intended sheet. A valid but incorrect address will not necessarily trigger an error. Also check for inconsistent layouts across sheets and for text in the address input that includes extra characters.

Limitations of INDIRECT

INDIRECT is a volatile function. Excel recalculates volatile formulas when recalculation occurs, even if their direct inputs have not changed. A workbook with a very large number of such formulas can become slower, particularly when many other calculations depend on them. For a small selector or summary, the convenience is often worthwhile; for a large model, consider whether a design with direct references or consolidated data would be easier to maintain.

Another important limitation is that INDIRECT cannot retrieve a reference from a closed external workbook. It is intended for references Excel can resolve in the current workbook or another workbook that is open. If the source file may be closed, consider importing or combining the data with Power Query, or redesigning the workbook so the required data is available locally. Power Query is a data-import and transformation option, not a drop-in formula replacement.

Excel for Microsoft 365 on Windows supports this formula, and the same basic function is available in Excel for Mac and Excel for the web. The interface for creating validation lists and managing files can differ by platform, but the formula syntax is the same. Excel’s regional settings can affect which list separator formulas use; the examples here use commas.

When a direct reference is better

If the worksheet is not meant to change, a direct reference is simpler, easier to audit, and does not rely on text being assembled at calculation time. For a fixed Sales sheet and fixed B2 cell, enter =Sales!B2. If the sheet name contains a space, Excel uses quotes; for a fixed Quarterly Results sheet, enter ='Quarterly Results'!B2.

For a small, fixed set of choices, direct references in separate formulas or a carefully designed lookup structure may also be clearer than constructing arbitrary references. The main trade-off is flexibility: a formula with INDIRECT can respond to a sheet name typed into a cell, while a direct formula explicitly shows which worksheet it uses. Choose the flexible option when users genuinely need to switch sheets; choose direct references when clarity and maintainability matter more than switching.

Frequently Asked Questions

Can Excel use a cell value as the worksheet name in a formula?
Yes. Put the worksheet name in a cell and use INDIRECT to turn that text into a reference. The worksheet must exist, and the rest of the reference, such as the target cell address, must be valid.

Do I need quotation marks around the sheet name?
Using single quotation marks around the name is a reliable habit, especially when names contain spaces or punctuation. If the name contains an apostrophe, double that apostrophe inside the reference; the example using SUBSTITUTE handles it.

Can the target cell address come from another cell?
Yes. Store an address such as B2 as text in a second cell and concatenate it into the reference. Make sure the text is a valid address and does not include an extra equals sign or worksheet name.

Why does my formula return #REF!?
The sheet name may be misspelled, the sheet may have been renamed or deleted, the address may be invalid, or the input may contain unwanted spaces. Check the tab name and target address before changing the formula.

Does INDIRECT work with a closed workbook?
No. It cannot resolve a reference to a closed external workbook. If the source workbook may be closed, use another approach, such as bringing the data into the current workbook with Power Query.

Will this formula update when I rename the worksheet?
Not reliably as a text-based reference. Because the sheet name is stored as text in a cell, Excel does not treat it like a normal direct reference that it can rewrite during a rename. Update the selector cell or the formula’s source data after renaming the sheet.

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.