Cell Referencing

• Jul 27, 2021
• 8 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.

The ASPxSpreadsheet control supports the following cell reference types.

• Relative, Absolute and Mixed References

• Cross-Worksheet Cell References

• 3D Cell References

• External Cell References

• Structured Cell References

Relative, Absolute and Mixed References

By default, a 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.

The following table provides examples of 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 or not it should be automatically adjusted when the formula is copied.

• Relative Cell References

• Absolute Cell References

• Mixed Cell References

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 (!). You can reference a cell from another worksheet by clicking it or type the corresponding reference to the cell’s editor.

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 = SUM(Sheet2:Sheet5!B2:C3) formula sums all values in cells B2 through C3 located on worksheets Sheet2, Sheet3, Sheet4 and Sheet5, shown in the following image. 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.

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. For example, =’c:\Temp[Book1.xlsx]Sheet1’!B3.

If you set the current file name of the source workbook (the WorkbookSaveOptions.CurrentFileName property), you can create external references to its cells without specifying the entire path to this workbook file. In this case, an external reference includes the specified file name of the source workbook in square brackets ([]), the worksheet name, an exclamation point (!) and the cell reference.

This example demonstrates how to programmatically create an external reference to the B3 cell in the Book1.xlsx workbook loaded into a non-visual spreadsheet component instance (the Workbook object). To use a non-visual spreadsheet component, add a reference to the DevExpress.Docs.v25.2.dll library.

• C#
• VB.NET

using DevExpress.Spreadsheet;
// ...

// Access the current workbook where you wish to use external references.
IWorkbook workbook = ASPxSpreadsheet1.Document;
Worksheet worksheet = workbook.Worksheets[0];

// Access the source workbook that contains the cells to be referred to from the current workbook.
Workbook sourceWorkbook = new Workbook();
sourceWorkbook.LoadDocument(@"c:\Temp\Book1.xlsx");

// Specify the file name for the source workbook. 
sourceWorkbook.Options.Save.CurrentFileName = "book.xlsx";

// Add the source workbook to the collection of external workbooks for the current workbook. 
workbook.ExternalWorkbooks.Add(sourceWorkbook);

// Create an external reference in the current workbook.
worksheet["C3"].Formula = "=[book.xlsx]Sheet1!B3";

Imports DevExpress.Spreadsheet
' ...

' Access the current workbook where you wish to use external references.
Dim workbook As IWorkbook = ASPxSpreadsheet1.Document
Dim worksheet As Worksheet = workbook.Worksheets(0)

' Access the source workbook that contains the cells to be referred to from the current workbook.
Dim sourceWorkbook As New Workbook()
sourceWorkbook.LoadDocument("c:\Temp\Book1.xlsx")

' Specify the file name for the source workbook. 
sourceWorkbook.Options.Save.CurrentFileName = "book.xlsx" 

' Add the source workbook to the collection of external workbooks for the current workbook. 
workbook.ExternalWorkbooks.Add(sourceWorkbook)

' Create an external reference in the current workbook.
worksheet("C3").Formula = "=[book.xlsx]Sheet1!B3"

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.

If the scope of the defined name is a worksheet in another workbook, specify this worksheet name in an external reference. For example, =[book.xlsx]Sheet3!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 image below illustrates examples of structured references using different special item specifiers and reference operators.

See Also

Online Demo: Functions in Formulas