Formula References — ReoGrid Docs

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.

Written	When copied
`A1`	both row and column shift
`$A1`	column pinned, row shifts
`A$1`	row pinned, column shifts
`$A$1`	neither 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).

Form	Example
Single cell	`Sheet2!A1`
Range	`Sheet2!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, and cycles are detected.

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

Situation	Result
The referenced row or column was deleted	`#REF!`
The referenced sheet was deleted	`#REF!`
A name that does not exist	`#NAME?`
Intersection of two ranges that do not overlap	`#NULL!`

#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 cell shifts its relative references to suit the destination. The same happens through the clipboard, including a paste from another application.

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

Range notation

Written	Meaning
`A1:C3`	an ordinary rectangular range
`A:C`	all of columns A through C
`3:5`	all of rows 3 through 5

Whole columns and rows are clamped to the sheet’s dimensions when evaluated.