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

HELPFUL RESOURCE:

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

 

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

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Calculate Elapsed Time in Excel When you have two points in time and you want to calculate the amount of time elapsed, then you will need to use Excel´s TEXT function Sometimes data gets dumped into Excel with the following date & time format: 24/01/2015  19:48:00. Using the TEXT function and enter...
Advanced SUMPRODUCT Function: Conditional Date If you want to find out the total sales for a particular month, then the SUMPRODUCT function is your answer.  You can create a criteria for a specific date range, a particular month or a year. In the example below I show you how to use the SUMPRODUCT function to sum up the tot...
Excel Subtotal Function – Avoid Double Count... What does it do? It returns a Subtotal in a list or database Formula breakdown: =SUBTOTAL(function_num, ref1) What it means: =SUBTOTAL(function number 1-11 includes manually-hidden rows & 101-111 excludes them, your list or range of data) ***Go to the bottom...
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 WORKBOOK Let us go through the steps in detail:   STEP...