Spreadsheets

INDIRECT to pull totals from whichever monthly tab you pick

Use INDIRECT with a month dropdown to pull category totals from whichever monthly tab you pick, plus the trade-offs of a volatile function and a table alternative.

Editorial illustration for INDIRECT to pull totals from whichever monthly tab you pick

INDIRECT turns text into a reference, so a month dropdown on a summary sheet can decide which monthly tab a SUMIFS reads. Build the sheet name from the dropdown value, wrap it in INDIRECT, and the same formula returns Groceries for August or September depending on your pick. It works, but it recalculates constantly and breaks if you rename a tab.

What does the workbook look like?

Say you keep one tab per month, named Aug26, Sep26 and Oct26. Each tab has the same layout: Date in column A, Category in column B and Amount in column C, rows 2 to 500. A separate tab called Summary has a month picker in B1 and a list of categories in A4 downward.

Set up the picker with Data, Data Validation, Allow List, and type the tab names separated by commas: Aug26, Sep26, Oct26. When you add a new month tab, add its name to that list.

What is the formula?

In B4 on the Summary tab:

=SUMIFS(INDIRECT("'"&$B$1&"'!$C$2:$C$500"), INDIRECT("'"&$B$1&"'!$B$2:$B$500"), $A4)

Read it from the inside out. The text starts with a single quote, then the tab name from B1, then a single quote, an exclamation mark and the range. For Aug26 the text becomes 'Aug26'!$C$2:$C$500, and INDIRECT turns it into a live reference. The quotes around the name are what let it work even if the tab name contains a space or a hyphen, such as Aug 26 or Aug-26.

To avoid a wall of #REF! errors when the picker is blank or wrong, wrap it:

=IFERROR(SUMIFS(INDIRECT("'"&$B$1&"'!$C$2:$C$500"), INDIRECT("'"&$B$1&"'!$B$2:$B$500"), $A4), "Tab not found")

What does it return in a worked example?

Say the Aug26 tab has three Groceries rows: ₹6,200, ₹3,450 and ₹2,900. Choose Aug26 in B1 and the Groceries row shows 6,200 plus 3,450 plus 2,900, which is ₹12,550. Switch B1 to Sep26 and the same cell instantly shows the September total, with no formula edits.

Picker valueText INDIRECT buildsGroceries result
Aug26'Aug26'!$C$2:$C$500₹12,550
Sep26'Sep26'!$C$2:$C$500Whatever Sep26 holds
Nov26'Nov26'!$C$2:$C$500Tab not found, until the tab exists

You can also pull a single cell with =INDIRECT("'"&$B$1&"'!E2"), which is handy if each month tab has a total in E2.

What are the downsides of INDIRECT?

  • It is volatile, which means Excel recalculates it every time anything in the workbook changes. With a few dozen formulas, you will not notice. With thousands, the file gets slow.
  • It breaks quietly if you rename a tab. Because the name is just text in a cell, Excel does not update it, and you will see errors or the wrong month.
  • It cannot read a closed workbook. If the monthly tabs live in separate files, INDIRECT only works while those files are open.
  • Auditing is harder. Excel's Trace Precedents cannot follow an INDIRECT reference, so reviewing the sheet takes more care.

Is there a better way than twelve tabs?

Often, yes. If every month sits in one table with a Month column, a SUMIFS with the month picker as a criterion gives the same result without INDIRECT at all. Convert the log to a table named Expenses, add a column Month with =TEXT([@Date], "mmmyy"), and use:

=SUMIFS(Expenses[Amount], Expenses[Category], $A4, Expenses[Month], $B$1)

There is no volatility, no tab names to keep in sync, and a new month needs no new tab, just new rows. One log also makes year-to-date and category trends easier, because everything is in one place.

How do you keep an INDIRECT workbook healthy?

  1. Name tabs consistently, and never rename them without updating the picker list.
  2. Keep the layout identical on every month tab, with categories in the same column.
  3. Add the IFERROR wrapper so a bad pick reads as a message, not a spill of errors.
  4. Test one month by comparing the Summary figure with a direct total on the tab itself.

Google Sheets supports INDIRECT in the same way, with the same quoting, so the formulas above work there unchanged.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports