Indirect Sheet Referencing To Calculate Ranges

The INDIRECT function returns a reference to a range. You can use this function to create a reference that won’t change if row or columns are inserted in the worksheet. Or, use it to create a reference from letters and numbers in other cells.

Download the example workbook here: Indirect Sheet Referencing to Calculate Ranges.xlsx

What does it do?

Returns a reference to a cell, or a range of cells of a sheet.

Formula breakdown:

INDIRECT(ref_text, [a1]) 

INDIRECT(Return the referenced range of a sheet, Omit if the reference is an A1 style or enter FALSE if it is a R1C1 style) 

Each month has the same data table structure for the same for Sales People

• Hugh Raye
• Justin Thyme
• Rick O’Shea
• Jacques Strap

Reference a Specific cell

In Cell I2 of each Month is the total Sales for that month. This formula example references the cell value to get that sheet’s I2 value

~	A	B	C
1	Month	Cell I2 Value	Formula
2	January	2,718,086	=INDIRECT(B13&”!I2”)
3	February	2,584,131	=INDIRECT(B14&”!I2”)
4	March	2,829,198	=INDIRECT(B15&”!I2”)

Reference a Range

Now, instead of relying on cell I2 in each sheet to have already calculated the Sales for that month, include the calculation in the INDIRECT function

~	A	B	C
1	Month	Total Sales	Formula
2	January	2,718,086	=SUM(INDIRECT(B19&”!D:D”))
3	February	2,584,131	=SUM(INDIRECT(B20&”!D:D”))
4	March	2,829,198	=SUM(INDIRECT(B21&”!D:D”))

Reference Table Names

You have to type the table Name and wrap the column header in square brackets like below. The easiest way to get that is by testing in a spare cell type = then select the table column to get the syntax

~	A	B	C
1	Table / Column Reference	Total Sales	Formula
2	JanTable[Sales]	2,718,086	=SUM(INDIRECT(B28))
3	FebTable[Sales]	2,584,131	=SUM(INDIRECT(B29))
4	MarTable[Sales]	2,829,198	=SUM(INDIRECT(B30))