Two Uses of INDIRECT In #Excel

Here are two different ways to use the INDIRECT function in Excel:
(Note: Video version above, text version below)

Text version:

The INDIRECT function “returns a reference specified by a text string”. The syntax is =INDIRECT(ref_text,[a1]). ref_text is the text sting that you build to create the reference. a1, which is optional, indicates if you want the structure to be in the A1 format or the R1C1 format. Most use A1, and that is the default, so it is almost always left out of the structure.

Here we have a data range. I’ve used data validation to create drop down lists for the Month and Sales. I’ve also named the ranges for each month and salesperson using the Create from Selection option. You can see a post on both these features using the links below:

Data Validation
Create from Selection

Ind1

In the “Answer” cell we will put the following formula:
=INDIRECT(F16) INDIRECT(F17). The space between the two functions means “the intersection of”. So by referencing the Month and Sales fields of the drop down lists, which are named ranges, Excel will give us the value at the intersection of the two:

Ind2

Another use of INDIRECT is to create a dynamic range of cells to sum based on the reference to two control cells. Here we have a list of numbers. I want to add a range of numbers within this list, say from A5:A12. I can use the formula =SUM(A5:A12), but if I want to change it, I have to manually go into the formula and edit it, or highlight the range that I want to change it to:

INd3

Instead, we can use the INDIRECT function within the SUM function to dynamically create a range to add. The formula we will insert will be =SUM(INDIRECT(“A”&B2&”:A”C2)). The result of the INDIRECT function will be A5:A12, the 5 coming from the reference of cell B2 and the 12 coming from the reference to cell C2:

Ind4

Now, by just changing the numbers in cells B2 & C2, you can dynamically adjust the formula to add different ranges.

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, ,

2 Responses to Two Uses of INDIRECT In #Excel

  1. KinggT8lOhll4.jux.com August 17, 2014 at 1:32 am #

    When I initially commented I clicked thhe “Notify me when new comments are added”
    checkbox and now each time a comment is added I get several emails with the same comment.
    Is there any way you can remove people from that service?
    Many thanks!

Leave a Reply