Cell Referencing

• Jul 25, 2021
• 11 minutes to read

A cell reference is a set of coordinates that specify the position of a cell or cell range on a worksheet. Use cell references in formulas to obtain and process data contained in the corresponding cells. The results of formulas that use cell references are automatically updated each time the values of these cells are changed.

SpreadsheetControl supports the following cell reference types.

• A1 Cell References

• R1C1 Cell References

• Cross-Worksheet Cell References

• 3D Cell References

• External Cell References

• Structured Cell References

A1 Cell References

The A1 cell reference style is used by default. The A1 cell reference is a combination of column and row headings to which the cell belongs - a column letter is followed by a row number. By default, column and row headings are displayed at the top and at the left of a worksheet (see How to: Show or Hide a Row or Column).

The following table provides examples of A1 references to a single cell, and various cell ranges (including references to entire rows and columns).

When used in a formula, a cell reference can be relative, absolute or mixed, depending on whether it should be automatically adjusted when the formula is copied.

• Relative Cell References

• Absolute Cell References

• Mixed Cell References

R1C1 Cell References

You can also use the R1C1 reference style, where both rows and columns are numbered in a worksheet. To switch on this style in a workbook, use the DocumentSettings.R1C1ReferenceStyle property. A cell reference of the R1C1 style is a combination of “R” followed by the row number and “C” followed by the column number.

Like the A1 cell reference style, the R1C1 cell reference style also supports the relative, absolute and mixed reference types.

Note

If the R1C1 cell reference style is switched on in a workbook, A1 references cannot be used in formulas, and vice-versa. The style of cell references used in formulas is automatically adjusted when you change the active cell reference style in the workbook.

Cross-Worksheet Cell References

In formulas, you can use references to cells located in other worksheets. To do this, specify the worksheet name before the cell reference, and separate them by an exclamation point (!).

When using a worksheet name in a cell reference, enclose it in single quotation marks (‘) in the following cases.

• The worksheet name contains a character other than a letter or number (spaces, parentheses, braces, etc.).
• The worksheet name starts with neither a letter nor an underscore symbol (“_”).
• The worksheet name is the same as an A1 cell reference (“A1”, “$M$15”, etc.).
• The worksheet name starts with an R1C1 cell reference notation.

If you rename a worksheet, this worksheet name is automatically updated in all cell references where it is used.

• C#
• VB.NET

// Sum values of cells located in different worksheets.
workbook.Worksheets["Sheet1"].Cells["H15"].Formula = "= Sheet2!C3 + Sheet3!C5";

' Sum values of cells located in different worksheets.
workbook.Worksheets("Sheet1").Cells("H15").Formula = "= Sheet2!C3 + Sheet3!C5"

3D Cell References

3D references allow you to process data contained in the same cells on multiple worksheets within a workbook. To create a 3D reference, specify the range of worksheet names before the cell (or cell range) reference, and separate them by an exclamation point (!).

For example, the formula in the following image sums all values in cells B2 through C3 located on worksheets Sheet2, Sheet3, Sheet4 and Sheet5. If you insert other worksheets between Sheet2 and Sheet5 (for example, by creating new worksheets, or by duplicating or moving existing worksheets), the B2:C3 cell ranges of the added worksheets will be included into the calculation. If you remove worksheets from the Sheet2:Sheet5 worksheet range, the values of these worksheets are excluded from the calculation.

External Cell References

A reference that refers to a cell, cell range or defined name in another workbook is called an external reference. In order to use external references, all referenced workbooks should be included in the IWorkbook.ExternalWorkbooks collection of the workbook that contains the external references.

Note

Creating external workbooks with circular references between each other is not recommended. In this case, you can get incorrect calculation results, since our internal calculation engine does not trace dependencies between external workbook cells.

• An external reference includes the entire path to the workbook file, the workbook file name in square brackets ([]), the worksheet name, an exclamation point (!), and the cell reference.

• The ExternalWorkbookCollection.Add method with the alias parameter allows you to specify a custom workbook name that you can use in external references instead of the original file name or when the external workbook is not saved to a file. In this case, an external reference includes the specified workbook name in square brackets ([]), the worksheet name, an exclamation point (!) and the cell reference.

• An external reference to a defined name in another workbook includes the source workbook name, an exclamation point (!) and the defined name. For example, =book.xlsx!range_name.

Structured Cell References

Structured references allow you to refer to tables and different ranges within tables. A structured reference has the following syntax.

=TableName[[#Data],[ColumnName]]

• TableName is a table name (Table.Name).
• [#Data] is a special item specifier that refers to a data range of a table or table column (as in the current example). You can also use the following special item specifiers in structured references, to refer to specific parts of tables or table columns: [#All], [#Headers], [#Totals] and [#This Row].
• [ColumnName] is a table column specifier (TableColumn.Name). If it is not preceded by any special item specifier, the table column specifier refers to the column data range (excluding the column header and total cells).

The images below illustrate examples of structured references using different special item specifiers and reference operators.