| Syntax |
| INDIRECT(ref_text,[a1]) |
| Purpose / Description |
| Returns a reference to a range whose address is calculated as a text string in either A1 or R1C1 notation. The worksheet name may be specified as part of the reference, providing a way to dynamically reference ranges on other worksheets. |
| Arguments |
| Name |
Type |
Description |
| ref_text |
text string |
A constant, reference or nested expression that returns a valid Excel address (optionally including a worksheet or even a workbook name). If the name of a worksheet is included, and the worksheet name includes non-standard characters, the sheet name must be enclosed in apostrophes, just as a normal formula reference would. It may be a reference to a defined name, which is by far the simplest implementation. |
| [a1] |
logical |
An optional constant, reference or nested expression that returns a flag indicating whether the ref_text argument is in A1 notation or not. Defaults to TRUE if omitted. |
| Return Values |
| Type |
Specific Value |
Description |
| reference |
|
A reference to one or more cells at the location specified in ref_text, if it is a valid address within the targeted workbook. |
| error |
#VALUE! |
If the value passed to a1 is not a logical value and cannot be coerced to a logical. |
| error |
#REF! |
If the value passed to ref_text is not a valid range address for the workbook in which it is built or it is a reference into another workbook which is not open in memory. |
| error |
|
Any errors referenced by arguments are returned due to propagation of error values. |
| Notes / Comments / Advice / Warnings |
| Comment: |
This is quite a slow function relatively, and volatile, so its usage should be limited to where it is really necessary. |
| Advice: |
The ADDRESS function is useful to generate the addresses of ranges to be referenced by INDIRECT. Alternatively, R1C1 addresses can be calculated quite simply, and do not have the wrinkle that A1 does with column lettering. |
| Advice: |
Except when addressing a range only once, it is wiser to derive the address strings separately and then reference them from the INDIRECT functions, as this will improve the performance a little. When the INDIRECT recapitulation is triggered on every calculation event, it won't have to recalculate the address string as well, whereas, if that string is calculated inside the INDIRECT, it has to be recalculated every time as well. |
| Warning: |
This function is volatile, which means that its extensive use within a workbook can dramatically slow down recalculation. With nested address calculations, it can be very slow. |
| Examples: |
Open / download sample workbook: INDIRECT.xlsx |
| |