ADDRESS function

ADDRESS returns a cell address (reference) as text, according to the specified row and column numbers. Optionally, you can determine how the address is interpreted. The address can be interpreted as an absolute address (for example, $A$1), a relative address (as A1), or as a mixed form (A$1 or $A1). You can also specify the name of the sheet.

For interoperability the ADDRESS and INDIRECT functions support an optional argument. With this argument you can specify whether the R1C1 address notation instead of the usual A1 notation is used.

In ADDRESS, the argument is inserted as the fourth argument, shifting the optional sheet name argument to the fifth position.

In INDIRECT, the argument is appended as the second argument

In both functions, if the argument is inserted with the value 0, then the R1C1 notation is used. If the argument is not given or has a value other than 0, then the A1 notation is used.

If the R1C1 notation is used, ADDRESS returns address strings using the exclamation mark (!) as the sheet name separator, and INDIRECT expects the exclamation mark as sheet name separator. Both functions still use the dot (.) sheet name separator with A1 notation.


ADDRESS (row, column, abs, A1, sheet)

ADDRESS(1, 1, 2, "Sheet2") returns the following: Sheet2!A$1

If the cell A1 in sheet 2 contains the value -6, you can refer indirectly to the referenced cell using a function in B2. Enter =ABS(INDIRECT(B2)). The result is the absolute value of the cell reference specified in B2, which in this case is 6.

Trademarks | IBM Connections wiki