ExcelMatic

Saturday, September 19, 2009

Function Indirect()

Indirect()

This function is used to return the reference to text reference provided. This function converts the text reference to actual reference and fetches the value available in the address referenced. (It will be much clear when we will take a look at an example)

Syntax:

= Indirect (Text_reference, Reference_format)

Text_reference: This is the text form of cell address or reference from where the value is to be fetched

Reference_format: Reference format can either be TRUE or False. True indicates that text reference is in A1 style and False indicates text reference is in R1C1 format

Examples:

Let us take an example here. In cell A1 write, B1. And, in cell B1 put any value (for example, 10). Now in cell A5 write this function:

= A1

This function will return 'B1' as a result as we have referred to the cell A1's value.

There could be another case - if we want to get the value of cell which is mentioned in A1. In such case we make use of Indirect() function. This function will the reference (the address of the cell from which we need to fetch the value) from the cell specified in the function. Now, change the function in cell A5 to this:

= Indirect (A1)

This function will now return the value stored in the cell B1 (the cell which is mentioned in the cell A1)

These functions are used mostly in the case where the address or the cell to be referred to are dynamically created.