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.
You can use the indirect function in different situations.
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.
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”.
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”.
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.
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.
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.