A range can consist of contiguous cells or a single cell. Returns the number of individual ranges that belong to a multiple range. The result is the absolute value of the cell reference specified in B2, which in this case is 6. If the formula above is in cell B2 of current sheet, and the cell A1 in sheet 2 contains the value -6, you can refer indirectly to the referenced cell using a function in B2 by entering =ABS(INDIRECT(B2)). =ADDRESS(1 1 2 'Sheet2') returns the following: Sheet2.A$1 If this parameter is absent or set to another value than 0, the A1 notation is used. Row represents the row number for the cell referenceĬolumn represents the column number for the cell reference (the number, not the letter)Ģ: row reference type is absolute column reference is relative (A$1)ģ: row (relative) column (absolute) ($A1)Ī1 (optional) - if set to 0, the R1C1 notation is used. When storing a document in ODF 1.0/1.1 format, if ADDRESS functions have a fourth parameter, that parameter will be removed. A new fourth parameter with the value 1 will be inserted. When opening documents from ODF 1.0/1.1 format, the ADDRESS functions that show a sheet name as the fourth parameter will shift that sheet name to become the fifth parameter. Both functions still use the dot '.' sheet name separator with A1 notation. In case of R1C1 notation, ADDRESS returns address strings using the exclamation mark '!' as the sheet name separator, and INDIRECT expects the exclamation mark as sheet name separator. If the argument is not given or has a value other than 0, then the A1 notation is used. In both functions, if the argument is inserted with the value 0, then the R1C1 notation is used. In INDIRECT, the parameter is appended as the second parameter. In ADDRESS, the parameter is inserted as the fourth parameter, shifting the optional sheet name parameter to the fifth position. You can also specify the name of the sheet.įor interoperability the ADDRESS and INDIRECT functions support an optional parameter to specify whether the R1C1 address notation instead of the usual A1 notation should be used.

You can determine whether the address is interpreted as an absolute address (for example, $A$1) or as a relative address (as A1) or in a mixed form (A$1 or $A1). Returns a cell address (reference) as text, according to the specified row and column numbers.

Returns a number representing a specific Error type, or the error value #N/A, if there is no error.

Libreexcel