Spill reference (#)

What it does

A#

A# takes a reference to a cell that anchors a spilled dynamic-array result and returns a reference to the whole spilled region — however large it currently is.

The essential idea is deferred extent. B1# does not mean "B1 through B10"; it means "the region B1 spilled", and that region is resolved when the reference is used. A formula written against B1# follows the spill as it grows and shrinks, which is what makes dynamic arrays composable without helper columns or volatile functions.

• The operator forms spill-anchor reference identity. Successful evaluation returns a Reference whose shape is SpillAnchor (chapter 01's reference-shape table).
• The operator does not resolve spill size or even spill existence. "Spill existence and spill-range materialization are downstream resolver concerns, not operator-formation concerns."

So A# is a constructor, not a lookup. Asking whether anything actually spilled at A1 happens later, in whatever consumes the reference.

The contract also records that an already-spill-anchor target remains stable under a second application, so A1## does not compound into anything new.

Arguments

Arity is exactly 1; no optional arguments, no defaults. The operator is postfix.

The operand must be an anchor-shaped reference. The contract records the admitted runtime slice as A1-style single-cell anchors, already-tagged spill anchors, and non-A1 symbolic anchor names passed through as spill-anchor text — and records that multi-cell A1 areas are rejected as invalid anchors. A range is not an anchor: A1:B2# has no meaning under that slice.

Try it

The spill operator needs a worksheet spill. OneCalc cannot provide a spilled worksheet range in this preview.

Quirks

Result and edge cases

• Nothing spilled at the anchor. The reference still forms; the failure surfaces downstream when the region is resolved. The contract records downstream dereference failures as resolver-level #REF! behaviour.
• Anchor cell holds a scalar. A single-cell result is a spill of size 1 in the dynamic- array model; whether A# on such a cell is valid is not settled here.
• Invalid anchor shape. A multi-cell operand is rejected in the admitted slice, surfacing as #REF!.
• The reference is live. The extent is whatever the spill currently is, so a formula consuming A# recalculates when the spill's size changes. That is the operator's purpose, and also its main performance consideration.
• No coercion, no lifting. This operator sits outside the scalar coercion story entirely.

Errors

How to implement

Notes for implementers

• Keep formation and resolution separate, exactly as the contract does. An implementation that resolves the spill region inside the operator cannot represent "the anchor exists but the spill does not yet", and will report the wrong error at the wrong time.
• Validate anchor shape at formation: single cell, or an already-tagged anchor. Reject multi-cell operands rather than silently taking their top-left.
• Make the reference re-resolve on recalculation. A cached extent is a correctness bug that looks like a stale-value bug.
• Record the dependency on the anchor's spill extent, not merely on the anchor cell, or the calculation chain will miss updates when the spill grows.
• The operator interacts with workbook state (HostInteractionClass::WorkbookState); it is not a pure value function even though it takes one operand.

Rust code

This entry has no Rust implementation yet.

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• FUNC.OP_RANGE_REF — the static way to name a region, by corners. A1# and A1:C3 may designate the same cells today and different cells tomorrow.
• FUNC.OP_IMPLICIT_INTERSECTION — the operator that goes the other way, collapsing an array-or-reference result to a single value. @ and # are the two halves of the dynamic-array compatibility story: one refuses spilling, the other consumes it.
• SINGLE/_xlfn.SINGLE is recorded in chapter 05 as the compatibility/serialization representation of @. This Handbook has not recorded the corresponding compatibility-serialization name for #, and does not assert one here.
• ANCHORARRAY — commonly reported as the serialized form of # in older builds. Not recorded in this Handbook's data, and stated here only as a pointer for someone probing the file format, not as a claim.
• ROWS, COLUMNS, COUNTA, SUMPRODUCT — typical consumers that resolve the region.
• TAKE, DROP, CHOOSEROWS, SORT, FILTER — the modern array functions whose results are usually what # is pointed at.

Named variants

No named variants are recorded yet.