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.
row represents the row number for the cell reference.
column represents the column number for the cell reference (the number, not the letter)
abs determines the type of reference. With a value of 1 or empty, the reference type is absolute ($A$1). With a value of 2, row reference type is absolute; column reference is relative (A$1). With a value of 3, row reference type is relative; column reference type is absolute ($A1). With a value of 4, the reference type is relative (A1).
A1 is optional. If this argument is set to 0, the R1C1 notation is used. If it is absent or set to a value other than 0, the A1 notation is used
sheet represents the name of the sheet. It must be placed in quotation marks.
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.
Parent topic: Functions