Excel INDIRECT: Link to Another Tab with a Formula

Formulas & functions · Updated November 18, 2026

To refer to a cell on another worksheet using a sheet name stored in A1, enter =INDIRECT("'"&A1&"'!B2"). If A1 contains January, the formula returns the value in January!B2; the single quotes also let the reference work when a sheet name contains spaces.

What INDIRECT does

INDIRECT takes a reference written as text and turns it into a reference Excel can evaluate. In the example above, Excel joins the sheet name in A1 with the text !B2 to make a worksheet reference. This is useful when the tab you want to read can change, because you can change A1 instead of rewriting the formula.

For a fixed worksheet, you usually do not need INDIRECT. A direct reference is simpler to read and generally easier to maintain. For example, with a worksheet named January and a value in its B2 cell, a formula on another sheet can refer directly to it. In Excel, a quick way to create a direct reference is to type = in the destination cell, click the source tab, click the source cell, and press Enter. Excel writes the reference for you.

INDIRECT is most useful when the reference itself needs to be assembled from text, such as a sheet name selected from a dropdown. It does not create a clickable link or move you to another tab: it returns a value from the referenced cell. If you mean a clickable jump to a tab, see the alternatives section below.

Build a link to a tab step by step

For this example, assume A1 contains the worksheet name January, and you want the formula to return the value in cell B2 on that sheet. The source worksheet must exist in the same workbook.

  1. On the sheet where you want the result, enter the tab name in A1, for example January.
  2. Select the cell where the returned value should appear.
  3. Enter =INDIRECT("'"&A1&"'!B2"). The formula uses the name in A1 and appends !B2 to create the reference.
  4. Press Enter. If January!B2 contains 125, the formula displays 125.

The single quotes around the sheet name are intentional. They are accepted for ordinary names too, and they are needed when a sheet name contains spaces or certain punctuation. For example, if A1 contains Q1 Sales, the assembled reference is ‘Q1 Sales’!B2. The worksheet name must match the tab name, including spaces.

Use a cell address that can change

You can store the address as well as the tab name in cells. This makes the destination adjustable without editing the formula. Suppose A1 contains January and B1 contains B2. With the sheet name in A1 and a cell address such as B2 in B1, enter =INDIRECT("'"&A1&"'!"&B1). If January!B2 contains 125, the result is 125. Changing B1 to C3 makes the formula return January!C3 instead.

This flexibility comes with a trade-off: Excel cannot check a text-built reference as easily as a direct reference. A misspelled tab name or an invalid address can cause an error. Keep the inputs controlled, and consider using a dropdown for sheet names if other people will edit the workbook. You can create a dropdown with Data > Data Validation, then choose List and provide the allowed names as the source. A dropdown reduces typing mistakes, but it does not automatically update its list when you add or rename tabs.

Handle spaces and apostrophes in sheet names

Spaces are handled by putting single quotes around the sheet name in the reference. The main example does this already, so it works for both January and Q1 Sales. Avoid removing those quotes if sheet names might change later; a formula that works with a simple name can otherwise fail after someone adds a space.

An apostrophe in a sheet name needs special handling: within a quoted worksheet name, Excel represents an apostrophe by doubling it. If A1 could contain a name such as O’Brien, use SUBSTITUTE to double any apostrophes before building the reference. With the sheet name in A1 and the desired source cell fixed at B2, enter =INDIRECT("'"&SUBSTITUTE(A1,"'","''")&"'!B2"). This transforms O’Brien into the quoted reference ‘O”Brien’!B2. The tab must actually have that name.

If users can type arbitrary text into the sheet-name cell, this extra step makes the reference more robust, but it cannot fix a name that does not exist. It also does not make INDIRECT a security boundary: treat workbook contents and formula inputs according to your normal file-sharing practices.

Common errors and how to troubleshoot them

  • #REF!: Check that the named tab exists, the spelling and spaces match, and the requested cell address is valid. A renamed or deleted sheet can leave the text in A1 out of date.
  • #VALUE!: Check for missing quote marks, an extra space in a cell address, or text that does not form a valid reference. The reference text must be assembled in the correct order: quoted sheet name, exclamation mark, then cell address.
  • Wrong value: Confirm the sheet name and address cells contain what you expect. Also check whether the source cell itself contains a formula or an unexpected value.
  • Formula appears as text: Make sure the destination cell is not formatted as Text. Change its number format to General, then re-enter the formula.
  • External workbook returns #REF!: INDIRECT generally cannot resolve references to a closed external workbook. Open the source workbook or use a different approach for data that must be retrieved while the source file is closed.

When troubleshooting, inspect the reference text you intend to construct one part at a time: sheet name, punctuation, and address. A tiny typo in text can prevent Excel from recognizing the reference, even when the underlying worksheet cell is valid.

Performance and workbook design

INDIRECT is a volatile function. That means Excel recalculates it when calculation occurs, even if its referenced inputs have not changed. A handful of INDIRECT formulas is usually manageable, but a large workbook with many such formulas can recalculate more than necessary and may feel slower. If the tab is fixed, use a direct reference. If you need to retrieve data by a key from a table, a lookup formula may fit the job better.

INDIRECT can also make formulas harder to audit. A direct reference tells a reviewer which cell is being used; a text-built reference may depend on values in several other cells. Document what the name and address input cells mean, and keep them close to the formulas that use them. If a workbook has many similarly structured monthly tabs, consider whether combining the data into one table with a month column would be easier to maintain than building many dynamic tab references.

INDIRECT versus direct references and clickable links

Choose the method based on what “link” means in your case:

  • Fixed value from another tab: Use a direct sheet reference. It is straightforward, and Excel can track the reference when worksheets are renamed.
  • Value from a tab selected by a cell: Use INDIRECT when the tab name is genuinely variable and the source is in the same workbook.
  • Clickable jump to a tab: Use a hyperlink rather than INDIRECT. With a worksheet named January and a desired destination of A1, a clickable internal link can be created with =HYPERLINK("#'January'!A1","Go to January"). This displays Go to January and takes the user to the target when clicked; it is navigation, not a formula that returns the target cell’s value.

A direct reference is generally the best default for a known tab. INDIRECT is a specialized option for a reference that must be built from text, not a general replacement for ordinary cell links.

Version and platform notes

INDIRECT is available in Microsoft Excel for Microsoft 365 on Windows and in current Excel versions on Mac and the web. The formula shown uses standard worksheet-reference syntax, so the core example is the same across those platforms. Ribbon labels and interface details can vary, but you do not need a special setting to enter the formula.

Excel’s default argument separator in the formulas here is a comma where a function has multiple arguments. Some regional settings use semicolons instead; if Excel rejects a formula that otherwise looks correct, check the list separator configured for your installation. The sheet-name and address logic remains the same.

Frequently Asked Questions

Can INDIRECT link to another tab in the same workbook?
Yes. Put the tab name in a cell and use INDIRECT to combine it with a cell address, such as B2. The formula returns the value in that referenced cell; it does not switch tabs.

How do I reference a sheet with spaces in its name?
Put single quotes around the sheet name in the reference. The example formula does this automatically, so a value like Q1 Sales in the name cell is handled as ‘Q1 Sales’!B2.

Why does INDIRECT return #REF!?
Most often, the text does not identify a valid reference. Check that the tab exists, its name matches exactly, the address is valid, and—if the reference is external—the source workbook is open.

Does INDIRECT make a clickable link?
No. It returns the value from a reference. To create a clickable jump to a worksheet cell, use Excel’s HYPERLINK function or insert a hyperlink through Excel’s interface.

Is INDIRECT better than a direct reference?
Not for a fixed source cell. Direct references are easier to follow and avoid INDIRECT’s volatile recalculation. Use INDIRECT when the worksheet or address must change based on text in the workbook.

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.