Indirect Sheet Referencing To Calculate Ranges

1 minute read

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))

Tags: lookup-reference, summing-counting, tables

Categories: formulas

Updated: October 19, 2018

Twitter Facebook LinkedIn

Leave a Comment