Using Indirect to Sum Data

The Indirect function in Excel enables you to perform actions based on a cell reference, a Sheet name, range names etc.  You can use Excel's Indirect function as a reference to a cell with a number or letter or sheet name in it.  Take the following as a basic example, in cell A1 put the number 2 and in cell A2 put the word Apples.  Now in cell A3 put this formula;

=INDIRECT("A"&$A$1)

The cell A3 will now display the formula result Apples.  A very simple example where "A" is hard coded into the Excel formula and the number 2 is in A1.  So join them together and Excel will give the output of the value in Cell A2.

Taking this concept one step further, if you put a value in cell A2 of Sheet2 and use the following formula;

=INDIRECT("Sheet2!"&"A"&$A$1)

The Excel formula will display the result of Sheet2 A2.

This concept can be extended to display a summary of all of the data in all of the sheets in an Excel workbook.  The formula has some limitations as the values in the formula are hard coded and assumes the data is all in the same format between sheets.  Here is an example;

 
=SUMIF(INDIRECT("'"&B$15&"'!B2:B10"),$A17,INDIRECT("'"&B$15&"'!$H$2:$H$10")) 
 
Where B15 is the Sheet Name.  B2:B10 is the Range, A17 is the Criteria andH2:H10 is the Range in the sheet which needs to be summed.  The following file shows how the data may be set out.  It is a duplication of a question I answered on Ozgrid some time ago where the person wanted to sum the data on all of the sheets with the sheet names in a cell on a summary sheet.

The following Excel file shows a practical example.