Excel 2020: Use INDIRECT for a Different Summary Report
February 10, 2020 - by Bill Jelen
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.
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.
Wrap the previous formula in
=INDIRECT() to have Excel pull the totals from each worksheet.
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.