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!.