What does it do?

Returns a reference to a range. The referenced range can be a cell, a range of cells, or a named range.

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 mystifies lots of Excel users and one that does not get that much fan fare, but I am about to change that for you!

To be totally honest, I wasn’t a big user of the INDIRECT function, but after seeing the various ways that it can be applied in to an Excel workbook, Financial Model or Excel Dashboard, I was hooked!

I will take you on this series in explaining how this function works, starting from the basics and moving on to real life practical uses that will take your Excel skills to another level!

DOWNLOAD EXCEL WORKBOOK

 

REFERENCED RANGE IS A CELL

=INDIRECT(G9)

When the referenced range is a cell, the INDIRECT function will go and return the content of the referenced cell.

Say we enter in cell G9 the following A1 style: D10

In another cell we enter =INDIRECT(G9)

This will indirectly return the value that resides in cell D10, which is the number 32 in our example below:

cell ref

 

REFERENCE A RANGE OF CELLS

=SUM(INDIRECT(C9:E9))

When the referenced range is a range of cells, the INDIRECT function will go and return the content of the referenced cells.

We can then enter a SUM function which will total the referenced cells.

Say we enter in cell I9 the following A1 style: C9:E9

In another cell we enter =INDIRECT(I9)

This will indirectly Sum the values that reside in cells C9:E9, which is 106 in the example below:

range of cells

 

REFERENCE IS A NAMED RANGE 

=SUM(INDIRECT(NamedRange))

 

When the referenced range is Named Range, the INDIRECT function will go and return the content of the Named Range.

We can then enter a SUM function which will total the Named Range.

We need to create a Named Range by selecting the data range and entering a name (with no spaces) in the Name Box:

named range

We then enter in cell K9 the following Named Range: TABLE

In another cell we enter =INDIRECT(K9)

This will indirectly Sum the values that reside in the Named Range TABLE, which is 1,007 in our example below:

named range sum

Indirect - Intro

 

Imagine having several Named Ranges that reference different data sets within a Workbook & adding a drop down menu to show the different Named Ranges.

Using this technique you can pick & choose the different data sets and with the INDIRECT function return the summation of each, thus creating an interactive Dashboard!

The possibilities are endless and in the next tutorials I will expand on such techniques.

 

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

Total Bonus Due With An Array Lookup Formula The LOOKUP function has three arguments, it is a like a simplified VLOOKUP function: What does it do? It looks up a value (lookup_value) in one range (lookup_vector) and returns a value from the same position in a second range (result_vector) Formula breakdown: =LOOKU...
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...
Consolidate with 3D Formulas in Excel 3D Formulas or References in Excel are a great way to consolidate data from multiple sheets. 3D Formulas reference several worksheets that have the same structure which allows you to consolidate by using the SUM function. Formula breakdown: SUM(Sheet1:Sheet4!A1) ...
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. ...