Ravindra BagaleCourses & study guides मराठी Track your progress

23. Formulas Across Sheets and Workbooks

23.5 INDIRECT for Sheet Names (and Its Pitfalls)

By Last updated:

Example first. Ruhi Bagale wants one cell where she picks a month (Jan, Feb or Mar) and a total that follows her choice. =SUM(INDIRECT("'"&$B$1&"'!B2:B5")) builds the address as text and turns it into a real reference. With Feb in B1 it shows ₹10,99,000.

INDIRECT with a sheet name — in 30 seconds

What: INDIRECT takes text that looks like an address, such as "'Feb'!B2:B5", and returns the real range. Build the text from a cell that holds the sheet name.

Why: A month picker or a store picker can switch a whole report to another sheet without rewriting formulas.

Tiny example: B1 = Feb. =SUM(INDIRECT("'"&$B$1&"'!B2:B5")) → ₹10,99,000. Change B1 to Mar → ₹11,87,000.

First look at the data (before → after):

INDIRECT with a sheet name: before and after tables, fictional Blinkit Maharashtra data

Expected result — Result for each choice in B1

B1 B3: month total (₹) B4: Pune (₹)
Jan 11,00,000 4,12,000
Feb 10,99,000 3,98,000
Mar 11,87,000 4,55,000
Before table and formula as text

Before — Sheet Report: B1 holds a sheet name chosen from a drop-down

Cell Value
A1 Month
B1 Feb (drop-down: Jan, Feb, Mar)

Formula / click path

B3: =SUM(INDIRECT("'"&$B$1&"'!B2:B5"))
B4: =INDIRECT("'"&$B$1&"'!B2")   (Pune for that month)

Do it step by step:

  1. On the Report sheet, type Month in A1.
  2. Select B1 and add Data › Data Validation › List with Source Jan,Feb,Mar.
  3. Choose Feb in B1.
  4. In B3 type =SUM(INDIRECT("'"&$B$1&"'!B2:B5")) and press Enter.
  5. In B4 type =INDIRECT("'"&$B$1&"'!B2").
  6. Switch B1 to Jan and Mar and check the totals against the table.

Why the single quotes? "'"&$B$1&"'!B2:B5" wraps the sheet name in single quotes, so names with spaces such as Mar Sales still work. Without them, only one-word names work.

Pitfalls (read before you use INDIRECT in a real report)

Pitfall What you see What to do
A sheet is renamed (Feb → Feb 2026) #REF!, because the text "Feb" no longer matches Keep the drop-down list and sheet names in sync, or use a Table + FILTER/XLOOKUP instead
Rows are inserted in the source sheet Still reads B2:B5; the text does not move like a normal reference Use a Table name inside the text, or a whole column
The source workbook is closed #REF! (INDIRECT cannot read closed files) Open the file, or use Power Query
Typo in B1 (Fbe) #REF! Use a Data Validation drop-down for B1
Volatile: recalculates on every change A big file feels slow Use INDIRECT in a few summary cells, not in thousands of rows
Hard to audit Trace Precedents shows nothing Write a note next to the cell explaining the address it builds

A safer alternative. If the months are stacked in one Table with a Month column, =SUMIFS(tblSales[Sales],tblSales[Month],$B$1) does the same job with none of these pitfalls. Use INDIRECT when the data really is split across sheets.

Practice task (try it before you open the answer):

  1. Build the Report sheet with the month drop-down.
  2. Add =INDIRECT("'"&$B$1&"'!B5") for Kolhapur.
  3. Rename the Mar sheet to Mar 2026, choose Mar in B1 and note what happens.
Answer

Kolhapur: Jan ₹1,89,000, Feb ₹1,76,000, Mar ₹2,01,000. After renaming, choosing Mar shows #REF! until you add Mar 2026 to the drop-down list and choose it.

Ravindra Bagale's Tip

Use INDIRECT for a picker on a summary page, never inside a 10,000-row calculation. It is the formula most likely to make a file slow and the hardest to audit.