Yes. In Excel, use INDIRECT to turn text containing a worksheet name and cell reference into a working reference—for example, =INDIRECT("'"&A2&"'!B2") when A2 contains a tab name. You can use the same pattern for several tabs, but each tab must be referenced in the formula; INDIRECT does not automatically discover every worksheet in a workbook.
How INDIRECT works with worksheet references
Excel normally sees a reference such as April!B2 as a direct instruction to read cell B2 on the April worksheet. INDIRECT takes a text string that looks like a reference and asks Excel to interpret it as one. That makes the worksheet name changeable: put a tab name in a cell, join it to the rest of a reference, and pass the result to INDIRECT.
In the formula above, the ampersands join three pieces: a single quote, the value in A2, and the text for the cell address. The resulting text might be 'April'!B2. The single quotes around the sheet name are useful even when the name has no spaces, and necessary when a tab name contains spaces or certain punctuation. Excel then reads the value from the referenced cell.
The key distinction is that INDIRECT does not look for worksheets by itself. The text you give it must resolve to a valid reference in the current workbook. If the tab name is misspelled, the cell address is invalid, or the sheet has been renamed or deleted, the formula can return #REF!.
Build a formula that follows a tab name
Start by putting the exact worksheet name in a cell, then build the reference around it. For example, this is useful when a report has a selector cell that lets a person choose which month to display.
With the tab name April in A2 and the value 1250 in April!B2, enter this formula in another cell: =INDIRECT("'"&A2&"'!B2"). It returns 1250. If you change A2 to May, the formula reads May!B2 instead, provided a worksheet named May exists.
- Enter a worksheet name exactly as it appears on the tab, such as April, in A2.
- Enter the formula in the cell where you want the result.
- Change A2 to another valid tab name and confirm that the displayed result changes.
To point to a different cell, change the address at the end of the text. For example, replace B2 with C5 if the value you need is in C5 on each worksheet. The address in the formula is text, so it must be joined inside the quotation marks and concatenation operators as shown.
Use INDIRECT to add values from several tabs
If a summary needs to total the same range on a chosen set of worksheets, list the tab names in cells and give each reference to SUM. This is clear and flexible when the set of tabs changes occasionally. It does require an INDIRECT reference for each sheet you want to include; a range of names alone does not make INDIRECT aggregate every sheet automatically.
With the worksheet names Jan, Feb, and Mar in A2:A4, and each worksheet containing monthly amounts in B2:B13, enter =SUM(INDIRECT("'"&A2&"'!B2:B13"),INDIRECT("'"&A3&"'!B2:B13"),INDIRECT("'"&A4&"'!B2:B13")). If the three ranges total 1200, 950, and 1100 respectively, the result is 3250. SUM adds the three referenced ranges.
For more tabs, extend the formula with another INDIRECT argument for each name. If the list is long or the workbook changes often, a different design may be easier to maintain. For a manageable number of sheets, however, this pattern makes the included worksheets visible in the formula rather than hiding the logic in a complicated construction.
Worksheet names with spaces or apostrophes
A tab called North Region works with the quoted-reference pattern: the resulting reference text is 'North Region'!B2. If A2 contains North Region and B2 on that sheet contains 480, the formula =INDIRECT("'"&A2&"'!B2") returns 480. Keeping the single quotes around every sheet name also makes the formula work consistently for simple names such as April.
Apostrophes inside worksheet names need special handling. Excel represents an apostrophe within a quoted sheet name by doubling it. For a tab named Director’s Report, use SUBSTITUTE to double the apostrophe in the name before constructing the reference.
With A2 containing Director’s Report and B2 on that worksheet containing 72, enter =INDIRECT("'"&SUBSTITUTE(A2,"'","''")&"'!B2"). The formula creates a correctly escaped reference and returns 72. If your workbook uses no apostrophes in tab names, the simpler formula is easier to read.
When a three-dimensional reference is simpler
If you need to total the same cell or range across a consecutive block of worksheets, Excel’s three-dimensional reference may be more suitable than INDIRECT. For example, =SUM(Jan:Mar!B2) adds cell B2 on every worksheet from Jan through Mar in the workbook’s tab order, including any worksheets positioned between those two tabs.
This method does not use a cell containing a changeable sheet name. It refers to the worksheets at the ends of a tab range, so its behavior depends on worksheet order. Moving a sheet into or out of that range can change which sheets are included. Choose it when the tabs form a stable block and you want a direct total; choose INDIRECT when the sheet name needs to come from a cell or formula.
A three-dimensional reference is not a general substitute for a list of unrelated tabs. For example, if you want Jan and Mar but not Feb, SUM with separate references or a list-driven design is more appropriate. Think about how worksheets are arranged and maintained before choosing between the two approaches.
Common INDIRECT errors and limitations
#REF!: Check that the sheet name in the selector cell matches the tab exactly, that the sheet still exists, and that the address is valid. Extra spaces in the name can be easy to overlook.- Wrong result or blank-looking result: Confirm that you built the intended address and that the source cell contains the value you expect. INDIRECT returns the referenced cell’s content; it does not search for a matching label elsewhere.
- Formula stops responding to a rename: INDIRECT receives a text string, not a normal direct reference that Excel can reliably update when a worksheet is renamed. Update the text or the name list after renaming a tab.
- External workbook reference fails: INDIRECT generally returns
#REF!for a reference to a closed external workbook. If the source workbook is another file, this limitation can make INDIRECT a poor choice.
INDIRECT is a volatile function, which means Excel recalculates it whenever a calculation occurs, rather than only when a directly referenced input changes. A few references are usually straightforward, but many INDIRECT formulas in a large workbook can add calculation work. If recalculation becomes slow, consider replacing stable references with direct links or using a data-import approach suited to the workbook.
In Excel for Microsoft 365 on Windows, the formula syntax is the same on Mac and Excel for the web. INDIRECT is available in those environments, though workbook behavior and the surrounding interface can differ. In particular, an external reference to a closed workbook remains a limitation; changing platforms does not make that reference reliable.
Choose the right method for the job
Use INDIRECT when the worksheet name is genuinely variable—for example, a drop-down selection chooses one month’s worksheet—or when a formula needs to construct a reference from text. It can make a template reusable without editing the formula every time the selected tab changes.
Prefer a direct reference such as =April!B2 when the worksheet will not change. Direct references are easier to audit, and Excel can track them more naturally when sheet names change. For totals across a continuous block of tabs, consider a three-dimensional SUM reference. For recurring reports assembled from many worksheets with a consistent layout, consider consolidating the data into one table or using Power Query rather than building a long chain of text-based references.
In a Microsoft 365 workbook, Power Query can be opened from Data > Get Data. The exact import steps depend on where the source data is stored and how the tabs are structured, but loading consistent data into one table can make later filtering and summaries easier. That is a larger change than adding one formula, so it is most useful when the workbook’s multi-tab structure is recurring and the formulas are becoming difficult to manage.
Frequently Asked Questions
Can INDIRECT refer to multiple sheets at once?
Yes, if you create a separate reference for each sheet and combine them with a function such as SUM. INDIRECT does not automatically expand a list of worksheet names into references to every tab.
Can I use a cell to choose which worksheet to read?
Yes. Put the exact tab name in a cell such as A2, then join it with a cell address inside INDIRECT. Changing the text in A2 changes the referenced worksheet, as long as that sheet exists.
Does INDIRECT work with worksheet names that contain spaces?
Yes. Surround the name with single quotes in the reference text, as in 'North Region'!B2. The formula examples above include those quotes, so the same basic pattern works for names with or without spaces.
Why does INDIRECT return #REF! after I close another workbook?
INDIRECT generally cannot resolve a reference to an external workbook when that workbook is closed. Keep the source workbook open or use a different method for retrieving data from a closed file.
Is INDIRECT better than a three-dimensional reference?
Neither is universally better. INDIRECT is useful when a sheet name changes based on cell text; a three-dimensional reference is simpler for the same cell or range across a consecutive block of tabs. Pick based on whether the worksheet names are dynamic and how the tabs are arranged.
Related
- Excel Strikethrough Shortcut: Windows, Mac, and More
- Stop Excel Converting Text to Dates Before It Happens
- Sorting by Dates in Excel: A Clear Step-by-Step Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Average Days Between Dates: Formula and ExamplesTo average the number of days between paired dates in Excel, subtract each start date from its matching…
- Excel PV of Future Value: Formula and ExamplesTo find the present value of a future amount in Excel, use the PV function with no periodic…
- SUMIF in Excel: Examples and How to Use ItExcel’s SUMIF function adds values that meet one condition. For example, with product names in A2:A7 and sales…
- NPV Calculator in Excel: Formula, Example, and TipsTo calculate net present value (NPV) in Excel, enter the discount rate and future cash flows, then use…
- Combine Day, Month, and Year in Excel with a Date FormulaTo combine a day, month, and year from separate cells into a real Excel date, use the DATE…
- Average in Excel Between Two Values: Formula & TipsTo average numbers between two values in Excel, use AVERAGEIFS with a lower-bound and an upper-bound condition. For…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.