Formualizer Docs

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 gave Engine::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

ArgumentTypeRequiredDefault
info_typeanyRequired—
referenceanyOptional—

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)

Input cells
A
142

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)

Input cells
A
142

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)

Input cells
A
142

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)

Input cells
A
110

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?.

FAQ

Which info types does CELL support?

contents, address, col, row, type and filename. Unsupported info types return #VALUE!.

Implementation

Engine source

On this page