How to pull data from multiple sheets into one summary sheet

Which method fits

INDIRECT with a list of sheet names

1. On a Summary sheet, type the sheet names in A2 downwards (North, South, West…), exactly as they appear on the tabs.
2. In B2, enter =INDIRECT("'"&$A2&"'!B10") to fetch cell B10 from the sheet named in A2.
3. Fill down. Add C2 with =INDIRECT("'"&$A2&"'!C10") for a second figure, and so on.

The single quotes around the sheet name make names with spaces (“North East”) work. INDIRECT builds the reference from text, so changing a name in column A changes which sheet is read.

When the cell isn't in the same place

If each sheet's total sits on a different row, look it up by its label instead of its address:

=XLOOKUP("Total", INDIRECT("'"&$A2&"'!A:A"), INDIRECT("'"&$A2&"'!D:D"), "missing")

Limits of INDIRECT

• Volatile: INDIRECT recalculates after every change in the workbook, which slows down large files.
• Fragile names: renaming a tab breaks the formula with #REF! until you update the list.
• Same workbook (or open workbooks) only: INDIRECT can't read closed files.

For whole tables rather than single cells, stack the sheets instead — see VSTACK and dynamic arrays or combining Excel sheets into one.

Google Sheets supports the same INDIRECT formula. To pull from other files, use IMPORTRANGE — see combining multiple IMPORTRANGE formulas.

Get the sheet names automatically

Typing tab names is error-prone. In Microsoft 365, a named formula can list them: define SheetNames in Name Manager as =TEXTAFTER(GET.WORKBOOK(1), "]") — this uses a legacy macro function, so save the file as .xlsm. Otherwise, keep the list by hand and add a check: =ISREF(INDIRECT("'"&A2&"'!A1")) returns FALSE for a name that doesn't match a tab.

3D references: the quickest total

When every sheet has the same layout and sits between two tabs in the workbook, a 3D reference sums, averages or counts the same cell across all of them without a sheet list:

=SUM(North:West!B10)        total of B10 on every sheet from North to West
=AVERAGE(North:West!B10)    average of the same cell
=COUNTIF(North:West!B10, ">50000")   does NOT work: COUNTIF doesn't accept 3D references

The range is defined by tab position, not names. A sheet dragged between North and West is included automatically; one dragged outside is dropped. A common trick is to add two blank sheets called Start and End and sum Start:End!B10, so anything placed between them is counted. Functions such as SUM, AVERAGE, MIN, MAX and COUNT accept 3D references; SUMIF, COUNTIF and the lookup functions do not.

Pulling whole tables with VSTACK

In Excel for Microsoft 365, VSTACK accepts a 3D range and returns every row from every sheet in one spill:

=LET(all, VSTACK(North:West!A2:D200),
     FILTER(all, CHOOSECOLS(all, 1) <> ""))

The FILTER removes the blank rows that come from sizing the range generously. VSTACK doesn't record which sheet a row came from, so if you need that, keep a Region column on each sheet. For larger tables or files that arrive separately, Power Query is sturdier; see consolidating data in Excel.

Checking the summary

Using the summary table above, three checks catch most errors:

• The column totals should match a 3D SUM: 48,200 + 61,950 + 39,400 = 149,550 for Q3, and =SUM(North:West!B10) should return the same.
• The Gap column should sum to the total minus the targets: 149,550 − 147,000 = 2,550, which equals −1,800 + 6,950 − 2,600.
• An =ISREF(INDIRECT("'"&A2&"'!A1")) check column should be TRUE on every row.

Troubleshooting

Which method when

• A monthly pack with one figure per sheet: INDIRECT with a sheet list, because you see each sheet's figure next to its name.
• Only a grand total: a 3D SUM between boundary sheets.
• Every row from every sheet, Microsoft 365: VSTACK. Older versions: Power Query or the sheet consolidation tool.
• Sheets in separate workbooks: open them and link, or better, combine the files first; see combining Excel files into one spreadsheet.

On Excel for Mac the same INDIRECT, 3D and VSTACK formulas work in Microsoft 365. The GET.WORKBOOK trick depends on Excel 4 macro functions, so test it on your version before relying on it. Google Sheets has no 3D references; use the INDIRECT list or the approaches in SUMIF across multiple sheets in Google Sheets.

Frequently asked questions

How do I pull the same cell from multiple sheets in Excel?

List the sheet names in a column and use =INDIRECT("'"&A2&"'!B10") next to each name, or =SUM(First:Last!B10) for a total.

Why does my INDIRECT formula return #REF!?

The sheet name in the cell doesn't exactly match a tab name, or the referenced workbook is closed.

Is there an alternative to INDIRECT?

Yes. 3D references, VSTACK in Microsoft 365, and Power Query all pull data from several sheets without volatile formulas.

Related guides

• How to combine tables in Excel
• How to create a pivot table from multiple sheets
• How to combine two pivot tables in Excel
• How to combine two sheets in Excel
• Combining data with VSTACK, HSTACK and other dynamic arrays