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

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...
Jump To A Cell Reference Within An Excel Formula When writing, editing or auditing Excel formulas you will come across a scenario where you want to view and access the referenced cells within a formula argument. This is helpful if you want to check how the formula works or to make any changes to the formula. There is ...
Vlookup Approximate Match in Excel What does it do? Searches for an approximate value in the first column of a table array and returns a value in the same row from another column (to the right) in the table array. Formula breakdown: =VLOOKUP(lookup_value, table_array, col_index_num, ) Wha...
Sum a Range Using the INDEX Function You can sum a range of values within a table using the INDEX function in Excel.  This is valuable when you want to extract key metrics from a table and put them in an Excel Dashboard. To make this work you firstly need to start your Excel formula with the SUM function foll...