Yes. In Excel for Microsoft 365, INDIRECT can turn a text reference into a reference to a cell on another sheet: with a tab named Sales and a value in its A1 cell, enter this formula in another cell: =INDIRECT("'Sales'!A1"). It returns the value from Sales!A1; if the sheet name is stored in a cell, INDIRECT can build the reference from that cell instead.
What INDIRECT does
Excel normally treats a reference such as a sheet name followed by a cell address as a link to a location. INDIRECT takes text that describes a location and asks Excel to interpret it as a reference. This distinction is useful when part of the reference needs to change based on a cell value, a selection, or a repeated layout.
INDIRECT has two arguments: INDIRECT(ref_text, [a1]). The required ref_text argument is text that represents a reference. The optional a1 argument controls the reference style: TRUE, or leaving it out, means A1-style references; FALSE means R1C1-style references. For most sheet-to-sheet formulas, the default A1 style is the one to use.
INDIRECT does not search for a matching sheet or decide what a label means. It interprets the text you give it. If that text does not form a valid reference, Excel returns an error, commonly #REF!.
Reference a fixed cell on another sheet
For a fixed destination, a regular sheet reference is usually simpler than INDIRECT. With the Sales Data tab’s A1 cell containing 125, enter this direct formula in a different sheet to return 125: ='Sales Data'!A1. The single quotation marks are needed because the tab name contains a space. For a tab name without spaces or special characters, Excel can often use an unquoted sheet name.
To make the same fixed reference through INDIRECT, with Sales Data!A1 still containing 125, use this formula in the destination cell; it also returns 125: =INDIRECT("'Sales Data'!A1"). The reference is enclosed in double quotes because INDIRECT expects text. The single quotes inside that text mark the sheet name as a quoted sheet reference.
For references to another tab in the same workbook, the key pattern is a sheet name, an exclamation mark, and a cell or range address. A sheet name containing spaces should be surrounded by single quotation marks inside the text. The outer double quotes belong to the Excel text string, not to the sheet name itself.
Build a reference from a sheet-name cell
A common use is to let a user choose a tab name in a cell. Suppose A1 contains the exact sheet name Sales Data, and B2 on that sheet contains the text North. Enter the following formula in a result cell on another sheet; it returns North: =INDIRECT("'"&A1&"'!B2").
The ampersands join pieces of text. The formula adds an opening single quote, the sheet name from A1, a closing single quote plus an exclamation mark, and the address B2. Quoting the name this way also accommodates spaces. Changing A1 to another existing tab name changes the referenced sheet without editing the formula.
For a more flexible reference, suppose A1 contains the tab name Sales Data and B1 contains the cell address D5. With D5 on Sales Data containing 42, enter this formula in another cell; it returns 42: =INDIRECT("'"&A1&"'!"&B1). This approach is handy for configurable reports, although it also means a typo in either input can make the reference invalid.
Use INDIRECT with ranges and summary formulas
INDIRECT can return a range reference, not just one cell. Suppose A1 contains the tab name January, and January!B2:B20 contains numeric sales amounts. Enter this formula in a summary cell; it returns the sum of those amounts: =SUM(INDIRECT("'"&A1&"'!B2:B20")). If the sheet name in A1 changes to February, the formula sums the same range on February.
This pattern can support a monthly summary sheet when each month has a separate tab with the same layout. It can also be used with functions that accept ranges, such as AVERAGE or COUNT. Make sure each relevant tab exists and uses the expected layout; INDIRECT does not check that a chosen sheet has the right kind of data.
A range reference is more than a string displayed in a cell: INDIRECT converts the text into a reference that another function can use. That is why SUM can total the range in the example. If you merely need to display a sheet name or a text label, you may not need INDIRECT at all.
Sheet names with spaces, apostrophes, and changing inputs
Spaces in a tab name are common, so build formulas with quoted sheet names even when the current name has no spaces. This helps avoid a formula breaking if a name is later changed to include a space. The text must still match the actual tab name exactly, including punctuation and spacing.
An apostrophe inside a sheet name needs special handling because single quotes mark the start and end of a quoted sheet name. Excel represents an apostrophe within a quoted sheet name by doubling it. For a tab named O’Brien, A1 containing O’Brien, and B2 on that tab containing Ready, use this formula in another sheet; it returns Ready: =INDIRECT("'"&SUBSTITUTE(A1,"'","''")&"'!B2"). SUBSTITUTE changes each apostrophe in the name to two apostrophes before the formula wraps the name in single quotes.
Dropdown lists can reduce typing errors in a sheet-name input cell. For example, a Data Validation list can offer valid tab names for users to select. A dropdown does not make INDIRECT detect renamed or deleted tabs automatically, though: the list itself must be maintained, and the selected name must still correspond to an existing sheet.
INDIRECT compared with a direct reference
Use a direct reference when the destination is known and is not meant to change. Direct references are easier to read, are less fragile when sheet names are edited through Excel, and avoid INDIRECT’s recalculation behavior. In a direct reference, Excel can update the reference when a sheet is renamed; a sheet name stored as text for INDIRECT may need to be updated manually.
INDIRECT is useful when a reference must be assembled from text, such as when a report selector chooses among tabs with identical layouts. It is not automatically a better way to link sheets. If there are only a few fixed cells to retrieve, direct references or a lookup table are often clearer. For a large workbook, consider whether a structured data table and lookup formula would be easier to maintain than a different tab for every period.
- Choose a direct reference for a stable, known cell or range.
- Choose INDIRECT when the sheet name or address genuinely needs to vary based on text.
- Consider a lookup or data-table design when the workbook is growing and the goal is to retrieve records by a key.
Common errors and workbook limits
#REF! usually means the text does not describe a valid reference. Check that the tab exists, that the spelling and spaces match, and that the address is valid. If the sheet name is held in a cell, check for leading or trailing spaces and confirm that the cell contains only the name expected by the formula.
#VALUE! can arise when the reference text is too long; Excel’s INDIRECT reference text is limited to 255 characters. A formula may also return an unexpected result if the text is assembled with a missing exclamation mark, an unmatched quote, or an address that points somewhere different from the intended cell.
INDIRECT is a volatile function. Volatile formulas recalculate when Excel recalculates, even when their referenced inputs have not changed, so many INDIRECT formulas can slow a large or calculation-heavy workbook. If performance becomes an issue, reduce unnecessary INDIRECT use or replace fixed text-built references with direct references where practical.
INDIRECT cannot use a reference to a closed external workbook. A formula that constructs a link to another workbook generally returns #REF! when that source workbook is closed. This is an important difference from ordinary external links, which can often retain links to a closed source file. For links across workbooks, test the exact setup and consider alternatives designed for importing or connecting data.
How to enter and check the formula
On Windows, select the destination cell, type the formula beginning with an equals sign, and press Enter. To check the referenced sheet, open the tab and inspect the cell or range used in the formula. If the input cell controls the sheet name, try changing it to another existing tab name and confirm that the returned value changes as expected.
Excel for Mac supports INDIRECT as well. The same formula syntax and general worksheet steps apply; ribbon layout and keyboard shortcuts can differ from Windows. Excel for the web also supports INDIRECT, though some workbook features and editing experiences differ from desktop Excel. In all versions, regional settings can affect whether formulas use commas or semicolons between arguments; the examples here use the comma separators specified for standard English-language Excel formulas.
When debugging a complicated text-built reference, inspect the text pieces separately. Confirm the name cell, address cell, quotation marks, and exclamation mark before looking for a problem in the value being returned. A formula can be syntactically valid while still pointing to the wrong sheet or cell, so verify the destination rather than relying only on whether Excel displays a value.
Frequently Asked Questions
Can INDIRECT reference a sheet whose name is in a cell?
Yes. Put the exact tab name in a cell and join it with the target address in the INDIRECT text. Quote the sheet name within the constructed reference, especially when it contains spaces.
Do I need single quotes around every sheet name?
Not always, but quoting the name is a reliable approach for names with spaces or punctuation. In a text-built reference, keep the single quotes as part of the reference text and the double quotes as Excel’s string delimiters.
Does INDIRECT update when I rename a sheet?
A direct reference is generally updated by Excel when its sheet is renamed. A sheet name held as text for INDIRECT may not be, so update the text source or formula after renaming the tab.
Why does INDIRECT return #REF! for another workbook?
INDIRECT cannot resolve a reference to an external workbook when that source workbook is closed. Open the source workbook to test the link, or use a different approach if the source must remain closed.
Is INDIRECT better than a direct sheet reference?
No. Use it when the reference needs to change dynamically from text. For a fixed destination, a direct reference is typically simpler and less prone to maintenance and recalculation issues.
Can INDIRECT refer to a whole range on another sheet?
Yes. Its text can describe a range, and a function such as SUM can use the resulting reference. Ensure the range address and sheet name are valid and consistent across the tabs you plan to use.
Related
- Excel Weekday Text: Show Dates as Day Names
- Excel WEEKDAY: Return a Day Name from a Date
- Excel NOW as Text: Show the Current Date and Time
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Copy Formulas in Excel Without Creating External LinksTo copy a formula without linking it to another workbook, copy the formula text from the formula bar…
- Excel UNIQUE Function for Data AnalysisIn Microsoft Excel for Microsoft 365 on Windows, use the UNIQUE function to create a live list of…
- Power Query Group By: Concatenate Text by GroupYes. Power Query’s Group By dialog does not offer a direct “concatenate text” operation, but you can group…
- Sort an Excel Column Highest to Lowest: Quick StepsTo sort numbers in Excel from highest to lowest, select a cell in the column and choose Data…
- COUNTIF Excel Partial Text: Match Words with WildcardsYes. To count cells that contain partial text in Excel, use COUNTIF with asterisks as wildcards. With the…
- Excel Text Join With a New Line: Formulas and TipsTo join text with a new line in Excel, use TEXTJOIN with CHAR(10) as the delimiter. With text…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.