V4 V5

Relative and absolute

ws.SetFormula(0, 2, "A1*B1");        // relative reference
ws.SetFormula(1, 2, "$A$1*B2");      // absolute reference (row and column pinned)
ws.SetFormula(2, 2, "$A1*B3");       // freeze columns only

ws.SetFormula(3, 2, "SUM(Sheet2!A1:A10)");        // cross-sheet reference
ws.SetFormula(4, 2, "SUM('My Sheet'!A1:B2)");     // a sheet name containing a space

$ means does not move when copied or auto-filled. Same as Excel.

WrittenWhen copied
A1both row and column shift
$A1column pinned, row shifts
A$1row pinned, column shifts
$A$1neither moves

$ versus shifting

An easy thing to conflate: $ applies when copying, not when rows and columns are inserted or deleted.

A formula references $A$1; you insert a row above row 1
  -> the reference becomes $A$2 (the target cell moved, so it followed)

That is Excel’s behaviour too. A reference points at that cell, not at that position.

Cross-sheet references

Another sheet is referenced as Sheet2!A1. Quote the sheet name when it contains a space or punctuation ('My Sheet'!A1).

FormExample
Single cellSheet2!A1
RangeSheet2!A1:B10
Quoted'My Sheet'!A1:B2

When a value on the referenced sheet changes, the formulas referencing it recalculate. Chains across several sheets (Sheet1 → Sheet2 → Sheet3) work. A cycle across sheets stops recalculating but does not produce #CIRCULAR!; the cells keep the values reached so far.

Even a data-only sheet with no formulas of its own notifies other sheets when its values change.

Defined names

A defined name can be used directly in a formula.

SUM(SalesData)
TaxRate * B5

A sheet-scoped name shadows a workbook-scoped name of the same spelling. For how to define them, see Defined Names.

When a reference breaks

SituationResult
The referenced row or column was deleted#REF!
The referenced sheet was deleted#REF!
A name that does not exist#NAME?
A name broken by deleting its range#REF!

#REF! rewrites the formula’s text in place. The original reference cannot be recovered (as in Excel). To undo it right after a row deletion, use Undo and Redo.

Copy and auto-fill

Copying and pasting a ReoGrid cell shifts its relative references to suit the destination. A formula pasted from another application is not shifted.

Auto-fill offsets references according to the direction you drag the fill handle. See Auto-fill.

Range notation

A range in a formula takes the form A1:C3. The whole-column A:C and whole-row 3:5 forms are not understood in formulas and give #NAME? (RangePosition.Parse and ws.ResolveRange accept them). A reference to a cell outside the sheet gives #REF!.

Was this article helpful?