CELL
Returns information about the address, formatting or contents of a cell.
Syntax:
CELL(info_type; cell_ref)
- returns information about the cell cell_ref. The information returned depends on the (case insensitive) text string info_type, which can be set as follows:
- COL
- CELL() returns the number of the referenced column. Example:
- CELL("COL"; D2) returns 4, as D is the fourth column.
- ROW
- CELL() returns the number of the referenced row. Example:
- CELL("ROW"; D2) returns 2.
- SHEET
- CELL() returns the number of the referenced sheet. Example:
- CELL("SHEET"; Sheet3.D2) returns 3 assuming Sheet3 is the third sheet.
- ADDRESS
- CELL() returns the absolute address of the referenced cell, as text. Examples:
- CELL("ADDRESS"; D2) returns $D$2.
- CELL("ADDRESS"; Sheet3.D2) returns $Sheet3.$D$2.
- CELL("ADDRESS";'X:\dr\test.ods'#$Sheet1.D2) returns 'file:///X:/dr/test.ods'#$Sheet1.$D$2.
- FILENAME
- CELL() returns the file name and the sheet number of the referenced cell, as text. Examples:
- CELL("FILENAME";D2) returns 'file:///X:/dr/myfile.ods'#$Sheet1, if the current document is X:\dr\myfile.ods and the formula is on Sheet1.
- CELL("FILENAME";'X:\dr\myfile.ods'#$Sheet1.D2) returns 'file:///X:/dr/myfile.ods'#$Sheet1.
- COORD
- CELL() returns the complete cell address in Lotus(TM) notation, as text. Examples:
- CELL("COORD"; D2) returns $A:$D$2.
- CELL("COORD"; Sheet3.D2) returns $C:$D$2.
- CONTENTS
- CELL() returns the contents of the referenced cell, without any formatting. Example:
- CELL("CONTENTS"; D2) returns the contents of cell D2 (or 0 if D2 is empty).
- TYPE
- CELL() returns text that indicates the type of cell contents:
- b (blank) signifies an empty cell
- l (label) signifies text, or the result of a formula as text
- v (value) signifies a number, or the result of a formula as a number
- WIDTH
- CELL() returns the width of the referenced column. The unit is the number of zeros (0) that fit into the column in the default text and the default size.
- PREFIX
- CELL() returns text that indicates the alignment of the referenced cell, as:
- ' = align left or left-justified
- " = align right
- ^ = centered
- \ = repeating (currently inactive)
- PROTECT
- CELL() returns the status of the cell protection for the cell.
- 1 = cell is protected
- 0 = cell is not protected
- FORMAT
- CELL() returns text that indicates the number format:
- , = number with thousands separator
- F = number without thousands separator
- C = currency format
- S = exponential representation, for example 1.234+E56
- P = percentage
- In the above formats, the number of decimal places after the decimal separator is given as a number. Example: the number format #,##0.0 returns ,1 and the number format 00.000% returns P3
- D1 = MMM-D-YY, MM-D-YY and similar formats
- D2 = DD-MM
- D3 = MM-YY
- D4 = DD-MM-YYYY HH:MM:SS
- D5 = MM-DD
- D6 = HH:MM:SS AM/PM
- D7 = HH:MM AM/PM
- D8 = HH:MM:SS
- D9 = HH:MM
- G = All other formats
- - (Minus) at the end = negative numbers are formatted in color
- () (brackets) at the end = there is an opening bracket in the format code
- COLOR
- CELL() returns 1, if negative values have been formatted in color, otherwise 0.
- PARENTHESES
- CELL() returns 1 if the format code contains an opening bracket (, otherwise 0.
Issues:
- CELL() returns information captured when the target cell was last updated. For example CELL("WIDTH"; D2) returns the width of column D - but if column D is then made wider, the value returned will not update until the contents of D2 are changed. To ensure an update, use (for example) CELL("WIDTH"; D2) + 0*RAND(); this works because RAND() updates when the column width is changed.
See Also
