DefinedName.RefersTo Property
Gets or sets a string that specifies a cell, cell range, formula or constant to which the defined name refers.
Namespace: DevExpress.Spreadsheet
Assembly: DevExpress.Spreadsheet.v24.1.Core.dll
NuGet Package: DevExpress.Spreadsheet.Core
Declaration
Property Value
| Type | Description |
|---|---|
| String | A string that is a cell reference, formula or constant associated with the defined name. If this value is empty or invalid, an ArgumentException is thrown. |
Remarks
Use the RefersTo property to set or obtain to which cell, range of cells, formula or constant the current defined name refers. To get more information on defined names, see the Defined Names document.
Note
Usually, references to individual cells or cell ranges in the RefersTo string are absolute and are preceded by the name of a worksheet that contains this cell or range. However, relative references are also supported.
If a defined name refers to a formula that uses another name (for example, as a function argument) and the scope of this “nested” name is a specific worksheet, insert this worksheet name before the “nested” defined name. If you do not specify a worksheet name explicitly, the “nested” name will be searched in the workbook’s collection of defined names. For example, see How to: Create Named Formulas.
A string returned by the RefersTo property starts from the equal sign (=). Even if you do not use the equal sign when setting the RefersTo property, it will be automatically added to the beginning of the assigned string.
The list below provides examples of RefersTo property values.
Refers to a Cell
“=Sheet1!$D$20” - refers to cell D20 located in worksheet Sheet1.
Refers to a Range of Cells
“=Sheet1!$A$1:$C$10” - refers to the range of cells A1:C10 located in worksheet Sheet1.
Refers to a Formula
“=SUM(Sheet1!$B$1,Sheet1!$B$10)” - refers to the formula calculating the sum of values contained in cells B1 and B10 located in worksheet Sheet1.
“=constantName_global+Sheet2!cellName” - refers to the formula that uses other defined names. This formula sums a constant value under the constantName_global name (this name’s scope is an entire workbook) and a value of a cell under the cellName name (this name’s scope is the Sheet2 worksheet).
Refers to a Constant
“=10.5” - refers to the constant value.
When you name a cell or cell range via the CellRange.Name property, the corresponding DefinedName object is automatically added to the Worksheet.DefinedNames collection of a worksheet that contains this cell or range of cells. This object’s RefersTo property is set to a string like “=worksheet_name!absolute_cell_reference”.
using DevExpress.Spreadsheet;
// ...
// Creates the "myRange" defined name with the RefersTo property set to "=Sheet1!$A$1:$C$3".
// The scope of defined name is worksheet "Sheet1".
workbook.Worksheets["Sheet1"].Range["A1:C3"].Name = "myRange";
When you add a new defined name to a worksheet’s or workbook’s collection of defined names by calling the DefinedNameCollection.Add method, this method’s second parameter sets the RefersTo property of the created DefinedName object.
Example
This example demonstrates how to define names for formulas. To do this, call the DefinedNameCollection.Add method with a name to be associated with a formula and the formula string passed as parameters. Use the Worksheet.DefinedNames or IWorkbook.DefinedNames property to access and modify the collection of defined names of a particular worksheet or entire workbook, depending on which scope you want to specify for a name.
Worksheet worksheet1 = workbook.Worksheets["Sheet1"];
Worksheet worksheet2 = workbook.Worksheets["Sheet2"];
// Create a name for a formula that sums up the values of all cells included in the "A1:C3" range of the "Sheet1" worksheet.
// The scope of this name will be limited by the "Sheet1" worksheet.
worksheet1.DefinedNames.Add("Range_Sum", "=SUM(Sheet1!$A$1:$C$3)");
// Create a name for a formula that doubles the value resulting from the "Range_Sum" named formula and
// make this name available within the entire workbook.
workbook.DefinedNames.Add("Range_DoubleSum", "=2*Sheet1!Range_Sum");
// Create formulas that use other formulas with the specified names.
worksheet2.Cells["C2"].Formula = "=Sheet1!Range_Sum";
worksheet2.Cells["C3"].Formula = "=Range_DoubleSum";
worksheet2.Cells["C4"].Formula = "=Range_DoubleSum + 100";