What does it do?
Returns a reference to a cell, or a range of cells of a sheet.
What it means:
=INDIRECT(Return the referenced range, Omit if the reference is an A1 style or enter FALSE if it is a R1C1 style)
The INDIRECT function is really cool as it can be used to reference sheet cells or a range of cells.
It opens up a lot of interesting possibilities as you can have fun creating flexible formulas, like indirectly summing the values that reside in another worksheet!
REFERENCED RANGE IS A CELL OF A SHEET
When the referenced range is a cell of a Sheet, the INDIRECT function will go and return the content of the referenced cell in that Sheet.
Sheet references are in the format of SHEETNAME!CELL, an example would be January!I3 which would mean the cell I3 in the worksheet named January.
Say we enter in cell C11 the worksheeet name: January
In another cell we enter =INDIRECT(C11&”!I3″), which will translate into =INDIRECT(“January!I3”) as the ampersand & will concatenate/join the two strings together.
NB: Highlighting the contents between the parenthesis and pressing the F9 key will confirm this:
This will indirectly return the value that resides in cell I3 in the worksheet named January, which has the total sales amount of $2,718,086 in our example below:
REFERENCE A RANGE OF CELLS OF A SHEET
When the referenced range is a range of cells of a Sheet, the INDIRECT function will go and return the content of the referenced cells of that Sheet.
We can then enter a SUM function which will total the referenced cells of that sheet.
Say we enter in cell G12 the worksheet name: February
In another cell we enter =SUM(INDIRECT(G12&”!D:D”)), which will translate into =SUM(INDIRECT(“February!D:D”)) as the ampersand & will concatenate/join the two strings together.
This will indirectly Sum the values that reside in column D in the worksheet named February, which is $2,584,131 in the example below (click to expand image):