How to Use Excel INDIRECT Function

How to Use Excel INDIRECT Function2016-04-29T10:07:54+00:00

Quick Intro

It allows you to create a dynamic cell reference by using text instead of actual reference. And, the best part is when you want to change the reference, you just have to change the text, not the actual reference.

Once you refer to a cell using the indirect function it will evaluate that reference on a real time and return the content of the cell.

The indirect function is one of those functions which are really unique in their usage. And, the best part is when you need to use this function it will work like a magic.

Syntax

INDIRECT(ref_text, [a1])

• ref_text A text which represents the address of a cell, an address of a range of cells, a named range, or a table name. For example, A1, B10:B20, or MyRange.
• [a1] A number or a boolean value to represent the type of cell reference you are specifying in ref_text.
• For example, if you want to use A1 reference style use TRUE or 1 and if you want to use R1C1 reference style use FALSE or 0 for R1C reference style. And, If you omit to specify cell reference type, it will use A1 style as default.

• When you referred to another workbook, that workbook should be opened.
• If you insert a row or a column in the range which you have referred, indirect will not update that reference.
• If you want to insert text directly into the function you have to put it in double quotation marks or you can also refer to a cell which has the text you want to use as a reference.

Examples

You can use the indirect function in different situations.

1. Reference to Another Worksheet

You can also refer to another worksheet using the indirect function. For this, you have to insert worksheet name in the indirect function.

In above example, I have used the indirect function to refer to another worksheet. I have the sheet name in cell A2 and cell reference in cell B2.

In cell C2, I have used the following formula to combine the text.

=INDIRECT(“‘”&A2&”‘!”&B2)

This combination creates a text which is used by the indirect function to refer to the cell A1 in sheet1. And, the best part is when you change the worksheet name or cell address the reference will automatically change.

Cell A1 in “Sheet1” has the value “Yes”.

So, that’s why indirect function returns the value “Yes”.

2. Reference to Another Workbook

You can also refer to another workbook, in the same way, we did for another worksheet. All you have to do, just add workbook name in your text which you are using as a reference.

In above example, I have used below formula to get the value from the cell A1 of the workbook “Book1”.

=INDIRECT(“[“&A2&”]”&B2&”!”&C2)

As I have workbook name in cell “A2”, worksheet name in cell “B2” and cell name in cell “C2”. I have combined them to use as an input text in indirect function.

Important Note: While combining cell reference as a text make sure to follow the right reference structure.

3. Using with Named Ranges

Yes, you can also refer to a named range using the indirect function. It’s just simple. Once you create a named range you have to enter that named range as a text in indirect function.

In above example, I have a drop down list in cell E1 which has a list of named ranges. And, in cell E2 I have used that name in the indirect function.

As range B2:B5 is named as “Quantity” and range C2:C5 is named as “Amount”. When I select quantity from drop-down indirect function instantly refer to the named range.

When I am selecting quantity from the drop-down, indirect function instantly refers to the named range “Quantity” and return the sum of that range.

And, when I am selecting the amount from the drop-down, I have the sum of cell range C2:C5.

What’s Next?

To learn more about Excel INDIRECT Function you can check Microsoft’s Help Section. And, if you have a unique idea to use the indirect function, I would love to hear from you.

You also have ADDRESS function which is highly useful. Apart from this, I have a list of excel functions and some real life formulas examples.

• Hidrogen Lim says:

what if my sum range from c2:c6?