Excel INDIRECT function

What does it do?

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

Formula breakdown:

=INDIRECT(ref_text, [a1])

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!

DOWNLOAD EXCEL WORKBOOK

 

REFERENCED RANGE IS A CELL OF A SHEET

=INDIRECT(“JANUARY!I3”)

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:

Excel INDIRECT function

 

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:

Excel INDIRECT function

 

REFERENCE A RANGE OF CELLS OF A SHEET

=SUM(INDIRECT(“FEBRUARY!D:D”))

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

Excel INDIRECT function

Excel INDIRECT function

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Advanced SUMPRODUCT Function: Sum Multiple Criteri... What does it do?It returns the sum of multiple criteria from the corresponding ranges or arraysFormula breakdown:=SUMPRODUCT((array 1 criteria) * (array2 criteria) * array values)What it means:=SUMPRODUCT((find my criteria in this array) * (find my criteri...
INDIRECT Function for Dependent Dropdown Lists in ... The INDIRECT function is really cool as it opens up a lot of interesting combinations in Excel.We will use the power of the INDIRECT function right now on creating Dependent Dropdown Lists.DOWNLOAD EXCEL WORKBOOKLet us go through the steps in detail: STEP...
Advanced SUMPRODUCT Function: Count You can use the SUMPRODUCT function to create advanced analysis of your data set.  For example, you can calculate the number of time a sales rep has made sales in a particular region.  Watch below to see how easy it is to create a count with this advanced SUMPRODUCT function....
Advanced SUMPRODUCT Function: Conditional Sum Another great way that you can use the SUMPRODUCT function is to create a conditional sum criteria.For example, you may want to find out how much sales were made above the $3,000 transactional level.  See how easy it is with this quick SUMPRODUCT example.DOWNLOAD WORKBOOK...