Cell Referencing
- 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
Cell references of these types are copied differently, depending on the reference type.
Cross-Worksheet Cell References
References to cells located in other worksheets.
-
References to the same cells located on multiple worksheets within a workbook.
-
References to cells located in other workbooks.
-
References to tables and table data ranges.
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).
| Cell Reference | Description |
|---|---|
| E7 | Refers to a single cell located at the intersection of column E and row 7. |
| A2:B5 | Refers to a range that includes cells from the top left cell A2 to the bottom right cell B5. |
| B:B | Refers to a range that includes the entire column B. |
| B:H | Refers to a range that includes the entire columns from B to H. |
| 5:5 | Refers to a range that includes the entire row 5. |
| 5:10 | Refers to a range that includes the entire rows from 5 to 10. |
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
A relative cell reference in a formula is based on the relative position of a referenced cell and a cell containing a formula. To keep this relative position unchanged, the cell reference is automatically changed each time you copy a formula to another cell. For example, if you copy a formula with a relative reference to cell B2 from cell C3 to D6, the reference will automatically change from B2 to C5.
Absolute Cell References
An absolute cell reference in a formula always refers to a specific cell, and it does not change if the formula is copied or moved to another cell. In absolute cell references, the column letter and row number are preceded by the ‘$’ sign. For example, if you copy a formula with an absolute reference to cell B2 from cell C3 to D6, the reference will remain unchanged ($B$2).
Mixed Cell References
A mixed cell reference in a formula can either be combined from an absolute reference to a cell column and a relative reference to a cell row (for example, $B2), or from a relative reference to a cell column and an absolute reference to a cell row (for example, A$1). If the formula is copied, the absolute element of the mixed reference (the column letter or row number preceded by the ‘$’ sign) will remain unchanged, and the relative element of the reference will automatically be adjusted.
For example, if you copy a formula with the $C3 mixed reference from cell B2 to D6, the reference will change to $C7.
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.
// 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.v18.2.dll library.
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";
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).
To combine column specifiers in structured references, you can use the following operators: colon - to refer to a range of two or more adjacent columns, comma - to refer to a combination of two or more columns, and space - to refer to an intersection of two or more columns.
The image below illustrates examples of structured references using different special item specifiers and reference operators.
