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