How to: Create Named Formulas
- 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.
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";
The image below shows how to use named formulas in worksheet cells (the workbook is opened in Microsoft® Excel®).