To use a cell’s text as a worksheet name in Excel, combine it with the cell or range you want and pass the resulting reference to INDIRECT. For example, if A1 contains January Sales and B2 on that tab contains 1,250, =INDIRECT("'"&A1&"'!B2") returns 1,250. The single quotes around the sheet name make the reference work when a tab name contains spaces.
INDIRECT is useful when a formula should follow a sheet selected from a list, such as a month or department. It also has important limitations: the reference is text, so Excel cannot update it automatically when a referenced tab is renamed, and INDIRECT is volatile, which can affect calculation speed in large workbooks.
What INDIRECT does
Excel normally interprets a worksheet reference directly, as in a formula that points to a particular tab and cell. INDIRECT takes text that looks like a reference and asks Excel to interpret that text as a reference. Its syntax is INDIRECT(ref_text, [a1]). The required ref_text argument is the reference written as text; the optional a1 argument determines whether Excel reads it as A1-style or R1C1-style notation. If you omit that second argument, Excel uses A1 style.
This distinction is the key to dynamic sheet names. A1 can contain a tab name, while the formula supplies the exclamation mark and cell address. When the text in A1 changes to another valid sheet name, INDIRECT points to the corresponding cell on that sheet. It does not search for a worksheet or create one; the named tab must already exist.
Build a reference from a sheet name
Suppose A1 contains January Sales, and cell B2 on the January Sales worksheet contains 1,250. The formula below assembles a reference to that cell. The apostrophes are part of the reference text, and the exclamation mark separates the worksheet name from the cell address.
With the sheet name in A1 and the value 1,250 in January Sales!B2, use this formula; it returns 1,250:
=INDIRECT("'"&A1&"'!B2")
The formula works with a simple tab name such as January too. Keeping the single quotes is a good habit because worksheet names containing spaces or certain punctuation need them. A fixed reference can use the same function, but INDIRECT adds no benefit if the tab will never vary.
For a fixed tab name with a space, this formula returns 1,250 when January Sales!B2 contains 1,250:
=INDIRECT("'January Sales'!B2")
Use a dropdown to choose the worksheet
A dropdown makes a dynamic reference easier to use and reduces typing mistakes. Create a small, maintained list of valid worksheet names, then use Data Validation to let a user select one. In Excel for Microsoft 365 on Windows, select the cell for the dropdown and choose Data > Data Validation. Set Allow to List and set Source to the cells containing the tab names. You can also enter a short comma-separated list directly in Source. The list is not automatically generated from worksheet tabs, so update it when tabs are added, removed, or renamed.
For example, if A1 is the dropdown and it contains January, February, or March, each monthly sheet can use the same layout, with the desired total in B2. The formula below returns the B2 value from whichever listed month is selected. If February is selected and February!B2 contains 980, the result is 980.
With the selected month in A1 and the monthly total in B2 on each month’s tab, use:
=INDIRECT("'"&A1&"'!B2")
Excel for Mac and Excel for the web also support INDIRECT. The formula syntax is the same; the precise placement of Data Validation commands can vary by interface. If you share the workbook, make sure the dropdown entries match the tab names exactly, including spaces.
Make the cell address dynamic too
The sheet name is not the only part of a reference that can come from a cell. You can also store a cell address as text and join it to the selected sheet name. This is useful for a report where the user chooses both a worksheet and a target cell, but it is easier to break than a fixed address: a typo in either input produces an invalid reference.
With a sheet name in A1, an address such as C5 in B2, and 73 in C5 on the selected sheet, the formula returns 73:
=INDIRECT("'"&A1&"'!"&B2)
Here, B2 must contain an address that Excel can interpret, such as C5. If the address itself is assembled from a row and column number, check the resulting text carefully. INDIRECT does not convert a descriptive label such as Revenue into a cell address unless that text is a valid defined name.
Handle apostrophes in worksheet names
Most sheet names need only the usual surrounding apostrophes. A less common edge case occurs when the tab name itself contains an apostrophe, such as O’Brien. In a worksheet reference, an apostrophe inside the name must be doubled. When constructing the reference from a cell, SUBSTITUTE can make that adjustment before INDIRECT reads the text.
With O’Brien in A1 and 42 in O’Brien!B2, the following formula returns 42:
=INDIRECT("'"&SUBSTITUTE(A1,"'","''")&"'!B2")
The doubled apostrophe in the reference text represents one apostrophe in the actual tab name. If you do not need to support names containing apostrophes, the simpler formula is easier to maintain. Avoid adding extra spaces to the name cell: a leading or trailing space can make the constructed reference fail to match the tab.
Common errors and how to fix them
The most frequent error is #REF!, which means Excel cannot resolve the text into a valid reference. Check the spelling of the worksheet name, whether the tab exists, the exclamation mark, and the address. A sheet name with spaces must be enclosed in apostrophes. Also check for invisible leading or trailing spaces in the cell containing the name. A dropdown sourced from a carefully maintained list can prevent many of these errors.
A #VALUE! error can occur when the reference text is too long or otherwise unusable; INDIRECT has a limit of 255 characters for its ref_text argument. If the reference appears correct, inspect the exact text being assembled by temporarily joining its pieces in a helper cell. This makes missing punctuation and unintended spaces easier to spot.
Another confusing behavior occurs after renaming a worksheet. A normal direct reference is generally adjusted by Excel when its sheet is renamed, but a name embedded in a text string is not a normal reference for Excel to update. If A1 still says January after the tab has become Jan, INDIRECT continues trying to use January and returns an error. Update the text or dropdown source as well as the tab name.
Performance and external workbook limits
INDIRECT is a volatile function. Excel recalculates volatile formulas during calculation even when their particular inputs have not changed. A few such formulas are usually unremarkable, but a workbook with many INDIRECT formulas, long chains of dependent formulas, or large ranges can recalculate more slowly. Use it only where the flexibility is useful, and avoid copying it across thousands of cells without considering the impact.
INDIRECT also cannot retrieve a reference from a closed external workbook. A text reference that points to another workbook may work while that workbook is open, but it returns #REF! when the external workbook is closed. If a process depends on values from other files, consider a direct external link, Power Query, or a consolidated table instead, depending on how the data is maintained. Do not assume that INDIRECT will refresh data from a closed file.
When a direct reference or another approach is better
If a formula should always point to one known worksheet, use an ordinary reference. For example, a direct reference to January Sales!B2 can be written with the sheet name in apostrophes; unlike a text-based reference, Excel can track that relationship when the tab is renamed. This is simpler to inspect and does not make the formula volatile.
If your goal is to add the same cell across several adjacent worksheets, a three-dimensional reference may be a better fit than building separate INDIRECT references. Such a reference can include every worksheet between two tab endpoints, so the tabs’ order matters. For recurring summaries across monthly sheets, a single data table with a month column can be easier to filter, analyze, and extend than a workbook with a growing set of similarly structured tabs. Power Query can also combine data from repeated tables when a refreshable consolidation is more appropriate.
Choose INDIRECT when the user genuinely needs to switch a reference by changing a sheet name or address. Choose a direct reference for a fixed destination, and consider a consolidated table or import process when the real need is to analyze records across many worksheets. The most maintainable solution is usually the one that makes the source of the result clear to the next person opening the workbook.
Frequently Asked Questions
How do I reference a sheet name in a cell with INDIRECT?
Put the exact worksheet name in a cell, such as A1, and join it to the cell address inside INDIRECT. For a reference to B2, use =INDIRECT("'"&A1&"'!B2"). The sheet must exist, and the name in A1 must match its tab.
Do sheet names with spaces need apostrophes?
Yes. A reference to a tab named January Sales needs apostrophes around the worksheet name in the reference text. Using them consistently in the formula also works for simple names without spaces.
Why does INDIRECT return #REF! after I rename a sheet?
INDIRECT reads the sheet name from text, and Excel does not automatically rewrite that text when a tab is renamed. Update the name in the cell or list that supplies the reference. Also verify that the new tab name is spelled exactly as shown on the worksheet tab.
Does INDIRECT work with a closed external workbook?
No. INDIRECT cannot resolve a reference to a closed external workbook. It may work while the other workbook is open, but use another method if the source file must remain closed.
Is INDIRECT available in Excel for Mac and Excel for the web?
Yes. INDIRECT is available in those Excel versions, and the formula syntax is the same as in Microsoft 365 for Windows. Some interface details, including where data-validation controls appear, can differ.
Related
- Excel: Sum Filtered Rows with SUBTOTAL
- Excel Sum Duplicates: How to Total Repeated Values
- Excel: Sum a Filtered Column with SUBTOTAL
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Tell If Your Excel File Is Corrupted: Key SignsYou can suspect an Excel file is corrupted if Excel cannot open it, displays a repair warning, or…
- Excel INDEX Function: Chinese Data, Syntax, and ExamplesIn Excel, INDEX returns a value from a specified position in a range, and it works with Chinese…
- INDIRECT Excel Formula for a Different Sheet: ExamplesIf cell A1 contains a worksheet name and you want the value in B2 on that sheet, use…
- Excel Date Range Between Two Dates: Formulas and ExamplesTo calculate the number of days between two dates in Excel, subtract the earlier date from the later…
- Filter Excel Table by Text: Steps, Options, and FixesTo filter an Excel table by text in Microsoft 365 for Windows, select the drop-down arrow in the…
- Excel Trim Decimal Places: Format or Change ValuesTo trim decimal places in Excel, select the cells and choose Home > Number > Decrease Decimal to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.