INDIRECT Generator
Turn a piece of text into a live cell, range, or sheet reference.
INDIRECT
Turn a piece of text into a live cell, range, or sheet reference.
Text that spells out a reference, so literal text needs quotes - or build it from cells with & (for example A2&"!B2"). Point it at a sheet that exists in your file, or the formula returns #REF!.
TRUE for normal A1 references. FALSE only if ref_text is written in R1C1 style.
=INDIRECT("Sheet1!B2", TRUE)Worked Example
You have one sheet per month and cell A2 holds the sheet name you want to report on.
=INDIRECT(A2&"!B2", TRUE)Returns: With A2 = January the formula returns the contents of cell B2 on the January sheet. Change A2 to February and the same formula reads February!B2.
Checks Before You Paste
- •INDIRECT is volatile: it recalculates on every edit anywhere in the workbook, whether or not anything it depends on changed. A few are harmless, a few thousand will make the file crawl.
- •INDIRECT cannot read a closed workbook. A normal external link keeps working when the source is closed, because Excel stores the full file path with it, but the INDIRECT version returns #REF! the moment that file is shut.
- •Sheet names containing a space or punctuation must be wrapped in single quotes inside the text: =INDIRECT("'"&A2&"'!B2"). Without them, a sheet called Q1 Sales gives #REF!.
How INDIRECT works
Availability: Every version of Excel, Excel for the web and Google Sheets. The a1 argument accepts FALSE for R1C1-style text in both Excel and Sheets. In every version, INDIRECT can only resolve a reference to another workbook while that workbook is open.
INDIRECT converts a piece of text into a real reference. Give it the string "Sheet1!B2" and it returns whatever is in B2 on Sheet1, exactly as if you had typed the reference. Because the text can be assembled from other cells, the reference becomes something a formula can decide at calculation time - which sheet to read, which column, how many rows - instead of something fixed when the formula was written.
That is genuinely useful in a few places: a dependent dropdown whose validation source is =INDIRECT(B2) pointing at a named range, a summary that pulls the same cell from a sheet named in a control cell, and a range that must not adjust when rows are inserted, since =SUM(INDIRECT("A2:A100")) keeps referring to A2:A100 no matter what anybody does to the sheet. Everywhere else it costs more than it gives.
The costs are worth stating plainly. INDIRECT is volatile: it recalculates on every edit anywhere in the workbook, whether or not anything it depends on changed, and a few thousand of them make a file slow to type in. It breaks Excel's dependency tracking, so Trace Precedents shows nothing and a wrong reference is only discovered when the formula runs. It does not follow renames - rename a sheet and every ordinary formula updates while every INDIRECT string still spells the old name and returns #REF!. And it cannot read a closed workbook: a normal external link keeps working because Excel caches the path and the value, but the INDIRECT version fails the moment the source file is shut.
The text itself has to be a valid reference, spelled the way Excel would write it. Sheet names containing spaces or punctuation need single quotes inside the string - =INDIRECT("'"&A2&"'!B2") - and it is safer to include those quotes always, since they are harmless on names that do not need them. Before reaching for INDIRECT, check whether INDEX, CHOOSE, a structured table reference or XLOOKUP can express the same idea, because all of them are non-volatile and all of them survive a rename.
Syntax
=INDIRECT(ref_text, [a1])| Argument | Required | What it does |
|---|---|---|
ref_text | Required | Text spelling out a reference. Literal text needs quotes ("Sheet1!B2"); more often it is built with & from cell values, such as A2&"!B2" or "A"&B2. It can name a cell, a range, or a defined name. Anything that is not a valid reference returns #REF!. |
a1 | Optional | TRUE or omitted means ref_text is written in ordinary A1 style. FALSE means it is in R1C1 style, so "R2C3" refers to C2. Only set it when you are deliberately generating R1C1 strings. |
More worked examples
Dependent dropdowns. Named ranges called North and South each list that region's sales reps, and B2 holds the region chosen in a first dropdown.
=INDIRECT(B2)Returns: Used as the Data Validation list source for C2, it offers the North names when B2 reads North and the South names when B2 reads South.
The named ranges must exist and their names cannot contain spaces, which is why region names with spaces are usually stored with underscores and cleaned with SUBSTITUTE inside the INDIRECT.
One sheet per client, each with a total in B10. A2 on the summary sheet holds a client name such as Acme Holdings, with a space in it.
=INDIRECT("'"&A2&"'!B10")Returns: Returns the value in B10 of the Acme Holdings sheet. Change A2 to another client name and the same formula reads that sheet instead.
The single quotes inside the string are what makes the space survive. Without them the formula returns #REF!.
A total that must always cover exactly rows 2 to 100 of column A, even after somebody inserts rows in the middle.
=SUM(INDIRECT("A2:A100"))Returns: Always sums A2:A100. An ordinary =SUM(A2:A100) would expand to A2:A101 when a row is inserted; the text version does not adjust.
This is the one behaviour that cannot be reproduced by a non-volatile alternative, and the only reason to accept INDIRECT's cost in a simple sum.
Common mistakes
- #REF! after a sheet is renamed
- Ordinary references are updated by Excel when a tab is renamed; a string inside INDIRECT is just text and is not. Every formula pointing at the old name breaks at once, and there is no warning at rename time. Read the sheet name from a cell so there is a single place to fix, or avoid INDIRECT for cross-sheet references entirely.
- It cannot read a closed workbook
- =INDIRECT("'[Budget.xlsx]Sheet1'!B2") works while Budget.xlsx is open and returns #REF! as soon as it is closed. A normal link to the same cell keeps its last value. If the source has to stay closed, use a direct external link or Power Query rather than INDIRECT.
- Volatility that only shows up at scale
- Every INDIRECT recalculates on every change anywhere in the file, and it drags its dependants with it. A model that felt fine with fifty of them becomes unusable with five thousand. Where the reference only needs to move within a known range, INDEX returns a reference too and is not volatile.
- A string that is not quite a reference
- "A"&B2 gives #REF! when B2 is blank, because "A" alone is not a cell. Sheet names with spaces need embedded single quotes; names with an apostrophe in them need that apostrophe doubled. Put the assembled text in a spare cell to see exactly what INDIRECT is being handed.
- Invisible to auditing
- Trace Precedents draws nothing for an INDIRECT, Find and Replace on a range name does not touch the string inside it, and moving cells does not update it. A workbook that leans on INDIRECT is one nobody can safely refactor, which matters more than the recalculation time in a file that will be maintained for years.
Frequently Asked Questions
Because the sheet name lives inside a text string, and Excel only updates real references when a tab is renamed. The text still spells the old name, which no longer resolves. Store the sheet name in a cell and build the reference from it, so a rename is a one-cell edit rather than a search through formulas.
No. It returns #REF! until the source workbook is opened, unlike an ordinary external link, which keeps the cached value and the file path. If the data has to be available with the source closed, use a direct link, Power Query, or a scheduled copy of the values.
Wrap the name in single quotes inside the string: =INDIRECT("'"&A2&"'!B2"). Excel writes references to such sheets the same way, so all you are doing is reproducing what it would have typed. Including the quotes unconditionally is safe - they do no harm on names that would not need them.
It is a strong suspect, along with OFFSET, TODAY, NOW, RAND and INDEX with whole-column arguments. All of them are volatile, so they recalculate on every change and force everything downstream to recalculate too. Count them with a Find on "INDIRECT(" across the workbook; if the count is in the thousands, that is your answer.
INDEX is better whenever the target lies inside a range you can name up front, because it also returns a reference and is not volatile. INDIRECT earns its place only when the sheet or workbook name itself is variable, when Data Validation needs a named range chosen by another cell, or when a range must be pinned against row insertion.
Related Tools
Excel Data Validation
Build the dropdown lists that INDIRECT makes dependent on another cell.
OFFSET Generator
The other volatile reference builder, for ranges that shift by row and column counts.
INDEX MATCH Generator
The non-volatile way to return a reference chosen at calculation time.
Excel Formula Explainer
Useful for reading an assembled reference string apart when it returns #REF!.