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.