CELL funtion
From Apache OpenOffice Wiki
< Documentation | How Tos
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
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
- CELL() returns text that indicates the type of cell contents:
- 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)
- CELL() returns text that indicates the alignment of the referenced cell, as:
- PROTECT
- CELL() returns the status of the cell protection for the cell.
- 1 = cell is protected
- 0 = cell is not protected
- CELL() returns the status of the cell protection for the cell.
- 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
- CELL() returns text that indicates the number format:
- 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
