MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Use INDIRECT for a Different Summary Report


February 28, 2019 - by Bill Jelen

Excel Use INDIRECT for a Different Summary Report. Photo Credit: Brendan Church at Unsplash.com

Say that you want to build the following report, with months going down column A. In each row, you want to pull the grand total data from each sheet. Each sheet has the same number of rows, so the total is always in row 12.

The first formula would be =January!B12. You could easily copy this formula to columns C:F, but there is not an easy way to copy the formula down to rows 5:15.


This summary report has the months January through December in A4:A15. The words Week 1 through Week 5 and Total in B3:G3.

The INDIRECT function evaluates text that looks like a cell reference. INDIRECT returns the value at the address stored in the text. In the next figure, a combination of the ADDRESS and COLUMN functions returns a series of text values that tell Excel where to get the total.

A formula of =A4&"!"&ADDRESS(12,COLUMN()) returns January!$B$12 to cell B4. Copy throughout the data and each cell returns text that looks like the correct cell reference. This example continues in the next figure.

Wrap the previous formula in =INDIRECT() to have Excel pull the totals from each worksheet.

Wrap the previous formula in INDIRECT: =INDIRECT(A4&"!"&ADDRESS(12,COLUMN())) and Excel returns the number from the Grand Total row on the monthly worksheets.

Caution

INDIRECT will not work for pulling data from other workbooks. Search the Internet for Harlan Grove PULL for a VBA method of doing this.

Title Photo: Brendan Church at Unsplash.com


Bill Jelen is the author / co-author of
MrExcel LX – The Holy Grail of Excel Tips

A book for people who use Excel 40+ hours per week. Illustrated in full color.