# Cell references

A formula can refer to one or more cells and automatically update its contents whenever any of the referenced cells change. The values from other cells can be obtained using A1 notation which is a flexible way of pointing at different sources of data for the formulas.

The table below summarizes the most popular methods of referring to different cells in the workbook.

Type Current sheet Different sheet
Relative =A1 =Sheet2!A1
Absolute =$A$1 =Sheet2!$A$1
Mixed =$A1 =Sheet2!$A1
Circular (example)

A1=B1

whereas

B1=A1

Sheet1!A1=Sheet2!A1

whereas

Sheet2!A1=Sheet1!A1

Range =A1:C10 =Sheet2!A1:C10

# Referring to named expressions

This is a special case in HyperFormula. Upon creation you define the scope of the expression:

// define for a global scope
// sheet id not passed
hfInstance.addNamedExpression('MyGlobal', '=SUM(100+10)');

// define for a local scope
// sheet id passed
hfInstance.addNamedExpression('MyLocal', '=Sheet2!$A$1+100', 1);

And now you can use 'MyGlobal' and 'MyLocal' names.

HyperFormula is more limited than typical spreadsheet software when it comes to referring to named ranges. For more information about how HyperFormula handles named ranges, see this section.

# Relative references

Relative and absolute references play a huge role in copy and paste, autofill, and CRUD operations like moving cells or columns.

By default, all references are relative which means that when you copy them to other cells, the references are updated based on the new coordinates. There are two main exceptions though: the move operation and named expressions, both of which use absolute references. HyperFormula provides copy , cut and paste methods that allow for handling clipboard operations.

Cut and paste behaves a bit differently. If '=A1' is copied from cell B1 into B2 it will stay after being placed into B2.

Copy and paste will behave a bit different in a relative mean

  • if '=A1' will be copied from B1 into B2 cell it will be '=A2'.
Formula in A1 Action Result in A2
=B1+1

Copy A1

Paste to A2

=B2+1

This example shows the change after the move operation was done:

// build with a simple dataset
const hfInstance = HyperFormula.buildFromArray([
 ['=B2', '=A1', ''],
]);

// these are the coordinates for a move operation
const source = { sheet: 0, col: 1, row: 0 };
const destination = { sheet: 0, col: 2, row: 0 };

// move B1
const changes = hfInstance.moveCells(source, 1, 1, destination);

// you can see the changes inside the console
console.log(changes);

# Absolute reference

A reference to a column (a letter) or a row (a number) may be preceded with a dollar sign $ to remain intact when the cell is copied between different places.

Formula in A1 Action Result in A2 and A3
=$B$1+1

Copy A1

Paste to A2

Paste to A3

=$B$1+1

# Circular reference

Since HyperFormula does not embed any UI, it allows for the input of a circular reference into a cell. Compared to popular spreadsheets, HyperFormula does not force any specific interaction with the user (i.e. displaying a warning ) when circular reference happens.

When circular reference happens, HyperFormula returns #CYCLE as the value of the cell where the circular reference occurred. After some CRUD operation is performed, the error might disappear when it is no longer a cyclic dependency. No matter the outcome, other cells are calculated normally and the dependency graph is updated. It is non-blocking.

# The #REF! error

By deleting the cell that is referenced in a formula you make the entire formula no longer valid. As a result, you will get the #REF! error which indicates that there is an invalid address used in a cell.

Consider the following example:

Formula in C1 Action Result in B1
=A1+B1+20 Delete column A #REF!

The #REF! error may also occur in other specific situations:

  • When you copy and paste formulas containing relative references, or example:
Formula in B1 Action Result in A1
=A1+1

Cut from B1

Paste to A1

#REF!
  • When the VLOOKUP is told to look up values in a column whose index is out of the scope.
  • When the INDEX function is told to return values from rows or columns that are out of the scope.