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

Concatenate With A Line Break Excel's CONCATENATE functions joins two or more text strings into one string.  The item can be a text value, number, or cell reference.If you add a double quotation with a space in between " " then this will add a space between the texts selected on either side.You ca...
SUMIFS Function: Introduction The SUMIFS function allows you to Sum multiple criteria.For example, you can select one Sales Rep from a list of Sales Reps and select one Region from a list of Regions and return the Sum of those arguments from a Sales list.  See how easy it is...DOWNLOAD WORKBOOK...
How to Convert Formulas to Values  Have you ever had a scenario where you write a formula and just want to show the value output only and get rid of the formula?Here is an example of a formula: Well I do not need the formula, bit I do want the last names only....hard copied!Fortu...
Advanced SUMPRODUCT Function: Maximum Sales What makes the SUMPRODUCT function even more powerful is its ability to nest formulas, or in simple terms, add another function within the SUMPRODUCT function.Instead of getting the Total Sales for a region, we can extract the Maximum sales value simply by entering the MAX fu...