INDIRECT(ref_as_text, mode)

INDIRECT allows a text reference to be turned into a cell reference. For example:

INDIRECT("B10")

Returns the contents of cell B10 in the same way as a direct reference would (i.e. typing B10 into a formula).

The INDIRECT funtion is more useful when used with cell references. For example, consider the formula = INDIRECT(A1) / 100 in a cell, say C1. A1 contains the text A5 and the range A5..A10 contains numbers. The sum displayed in cell D1 can now be controlled by entering a different any of "A5", "A6", "A7"...."A10" in cell A1, as shown below:

image\spIndirect.gif

Mode is optional and can be set to true or false to switch between A1 style notation and R1C1 style notation. For example, the following will all return the contents of cell B10:

INDIRECT("B10")
INDIRECT("B10", TRUE)
INDIRECT("R10C2", FALSE)

See also:

Other lookup functions