Whena1is TRUE (the default value), INDIRECT evaluatesref_textas an “A1” style reference.
When a1 is FALSE, INDIRECT evaluatesref_textas an “R1C1” style reference.
For example:
Note: the a1 argument only changes the way INDIRECT evaluates ref_text, not the result.
Another reason is that you want to assemble a reference in a formula using different bits of information.
To Excel, it’s just a text value.
The text entered in column E represents different ranges.
The INDIRECT function then evaluates the text and converts it to a valid reference.
The formula is dynamic and responds to the sheet names in column B.
If the sheet names are changed, the formula will automatically recalculate.
For example, if a row is deleted in this range, the reference will become A1:A99.
The formula below will always refer to the first 100 rows of column A.
Example 6 - named range
The INDIRECT function can easily be used with named ranges.
The worksheet below contains twonamed ranges:Group1(B5:B12) andGroup2(C5:C12).
A specific example of this approach isusing named ranges to make dependent dropdown lists.
One example is this formula, designed tostrip numeric characters from a string.
