CELL
Returns requested information about a reference.
Supported info_type values (case-insensitive):
"contents"— the value of the upper-left cell of the reference"address"— the absolute A1 address of the upper-left cell (e.g.$A$1)"col"— the 1-based column of the upper-left cell"row"— the 1-based row of the upper-left cell"type"—"b"for a blank cell,"l"for text,"v"for any value"filename"— the directory,[file name]and sheet name of the reference (the formula's own sheet without one), e.g.C:\dir\[Book1.xlsx]Sheet1, from the path the host gaveEngine::set_workbook_path. An empty path means a workbook never saved, which gives empty text. With no path the workbook's identity is unknown, which gives#VALUE!: opening a file does not publish where it is stored.
Other info_type values (format, parentheses, prefix, protect,
width) and calls without a reference (except filename) return
#VALUE!, as do 3-D references and a non-reference argument for
address, col, row or filename.
An argument that evaluates to an error propagates that error unchanged.
The Formualizer engine in this repository at 0.15.0 plus unreleased changes computes the example results. Known differences from Excel are listed on this page.
Syntax
=CELL(info_type, [reference])
Arguments
| Argument | Type | Required | Default |
|---|---|---|---|
info_type | any | Required | — |
reference | any | Optional | — |
Differences from Excel
Without a reference Excel's filename names the sheet of
the cell selected at calculation time. There is no selection state here, so
it names the formula's own sheet.
Operation diagnostics report both gaps: a call without a reference as the
implicit_active_cell_reference mode, even with a path, and any call as the
filename mode while the workbook's identity is unknown.
CELL("contents", <blank cell>) produces Empty in-graph
where Excel produces numeric 0, so ISBLANK(CELL("contents",Z99)) is TRUE
here and FALSE in Excel; tracked in #333 and to be resolved with the #319
blank-coercion variant study.
Examples
Read the absolute address of a known input cell.
Formula =CELL("address",A1)
| A | |
|---|---|
| 1 | 42 |
Expected result $A$1
Try this formula
This editor runs the published Formualizer 0.14.0 package, so the last digits can differ and functions newer than that release return #NAME?.
Change the information type to read the cell's content.
Formula =CELL("contents",A1)
| A | |
|---|---|
| 1 | 42 |
Expected result 42
Try this formula
This editor runs the published Formualizer 0.14.0 package, so the last digits can differ and functions newer than that release return #NAME?.
Read the contents of a reference to an empty cell.
Formula =CELL("contents",B1)
Expected result 0
Try this formula
This editor runs the published Formualizer 0.14.0 package, so the last digits can differ and functions newer than that release return #NAME?.
An unsupported information type cannot identify a cell property.
Formula =CELL("unknown",A1)
| A | |
|---|---|
| 1 | 42 |
Expected result #VALUE!
Try this formula
This editor runs the published Formualizer 0.14.0 package, so the last digits can differ and functions newer than that release return #NAME?.
CELL contents
Formula =CELL("contents",A1)
| A | |
|---|---|
| 1 | 10 |
Expected result 10
Try this formula
This editor runs the published Formualizer 0.14.0 package, so the last digits can differ and functions newer than that release return #NAME?.
CELL address of the top-left cell
Formula =CELL("address",A1)
Expected result $A$1
Try this formula
This editor runs the published Formualizer 0.14.0 package, so the last digits can differ and functions newer than that release return #NAME?.
Related functions
FAQ
Which info types does CELL support?
contents, address, col, row, type and filename. Unsupported info types return #VALUE!.