MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2020: Use INDIRECT for a Different Summary Report


February 10, 2020 - 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


This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.


Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.