How to: Create Named Formulas

Jun 07, 2019
2 minutes to read

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 Workbook.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.

View Example

FormulaActions.cs
FormulaActions.vb

csharp

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";

Dim worksheet1 As Worksheet = workbook.Worksheets("Sheet1")
Dim worksheet2 As Worksheet = 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"