Formula Cell References

In This Topic

You can create interdependencies among cell formulas by the means of cell references. Cell references give you the power to calculate a value for one cell based on another cell's value. In a cell's formula, you can refer to a cell from the same or another formula sheet. The string, which you use in a formula to reference a cell, is called cell reference.

The cell reference to another sheet follows the following form:

SheetRef!CellRef

where:

SheetRef - is the name of the sheet in which the cell resides.

! - is used to separate the SheetRef from the CellRef

CellRef- is the local cell name in the sheet

To reference a cell from the same sheet you can use the CellRef only.

Sheet Ref

Since formula sheets are designed to be used in different scenarios, the sheet itself has no direct knowledge of the external sheets it can reference, nor about the sheets which can reference it. It can only acquire a reference to a sheet with a certain name from its container. A formula sheet container is generally such an object, which hosts a formula sheet and implements the INFormulaSheetContainer interface. It is up to the container to "link" the sheet, which it contains to other sheets for which it has knowledge.

Cell Ref

Based on whether the referenced cell is a named section cell, or resides in a section row, the CellRef takes the following forms:

Named cell:
[SectionName.]CellName

where:
SectionName - is the name of the section. It is optional and can be omited, if you are sure that the sheet does not have another cell with the same name.
CellName - is the name of the cell, which was specified when the cell was added to the section Cells dictionary.
Row cell:
SectionName.ColNameRowIndex

where:
SectionName - is the name of the section.
ColName - is the name of the cell column. The cell column index is the 0 based index of the cell in its row. The name of a column with a concrete index is provided by the GetColName method of the section.
RowIndex - is the string representation of the 1 based row index in which the cell resides (e.g. first row is with index 1, section with index 2 etc).

The CellRef for a cell can always be obtained from the Name property of the NFormulaCell class. The following example demonstrates the different local cell references.

C#	Copy Code
// create a new sheet NFormulaSheet sheet = new NFormulaSheet(); // create the first section NFormulaSection section1 = new NFormulaSection("Section1", "Section1"); sheet.Sections.Add(section1); // create named cells in section1 // they are both constant cells section1.Cells = new NFormulaCellDictionary(); section1.Cells.Add("A", new NSingleFormulaCell(5)); // value of 5 section1.Cells.Add("B", new NSingleFormulaCell(2)); // value of 2 // create a row in section1 // the first cell references cell A from section1 // the second cell references cell C from section2 section1.Rows = new NFormulaRowCollection(); row = new NFormulaRow(); section1.Rows.Add(row); row.Add(new NSingleFormulaCell("A+2")); // value of 7 row.Add(new NSingleFormulaCell("C+2")); // value of 9 // create the second section NFormulaSection section2 = new NFormulaSection("Section2", "Section2"); sheet.Sections.Add(section2); // create named cells in section1 // cell C references cells A and B from section1 with their full names // cell D references cell A1 from section1 section2.Cells = new NFormulaCellDictionary(); section2.Cells.Add("C", new NSingleFormulaCell("Section1.A+Section1.B")); // value of 7 section2.Cells.Add("D", new NSingleFormulaCell("Section1.A1")); // value of 7 // create a row in section1 // the first cell references cell A1 from section1 // the second cell references cell B1 from section2 section2.Rows = new NFormulaRowCollection(); row = new NFormulaRow(); section2.Rows.Add(row); row.Add(new NSingleFormulaCell("Section1.A1+2")); // value of 9 row.Add(new NSingleFormulaCell("Section1.B1+2")); // value of 11

Copy Code

// create a new sheet 
NFormulaSheet sheet = new NFormulaSheet();

// create the first section
NFormulaSection section1 = new NFormulaSection("Section1", "Section1");
sheet.Sections.Add(section1);

// create named cells in section1
// they are both constant cells
section1.Cells = new NFormulaCellDictionary();
section1.Cells.Add("A", new NSingleFormulaCell(5)); // value of 5
section1.Cells.Add("B", new NSingleFormulaCell(2)); // value of 2

// create a row in section1 
// the first cell references cell A from section1
// the second cell references cell C from section2
section1.Rows = new NFormulaRowCollection();
row = new NFormulaRow();
section1.Rows.Add(row);

row.Add(new NSingleFormulaCell("A+2")); // value of 7
row.Add(new NSingleFormulaCell("C+2")); // value of 9

// create the second section
NFormulaSection section2 = new NFormulaSection("Section2", "Section2");
sheet.Sections.Add(section2);

// create named cells in section1
// cell C references cells A and B from section1 with their full names
// cell D references cell A1 from section1
section2.Cells = new NFormulaCellDictionary();
section2.Cells.Add("C", new NSingleFormulaCell("Section1.A+Section1.B")); // value of 7 
section2.Cells.Add("D", new NSingleFormulaCell("Section1.A1")); // value of 7

// create a row in section1 
// the first cell references cell A1 from section1
// the second cell references cell B1 from section2
section2.Rows = new NFormulaRowCollection();
row = new NFormulaRow();
section2.Rows.Add(row);

row.Add(new NSingleFormulaCell("Section1.A1+2")); // value of 9
row.Add(new NSingleFormulaCell("Section1.B1+2")); // value of 11

Visual Basic	Copy Code
Dim row As NFormulaRow ' create a new sheet Dim sheet As New NFormulaSheet ' create the first section Dim section1 As New NFormulaSection("Section1", "Section1") sheet.Sections.Add(section1) ' create named cells in section1 ' they are both constant cells section1.Cells = New NFormulaCellDictionary section1.Cells.Add("A", New NSingleFormulaCell(5)) ' value of 5 section1.Cells.Add("B", New NSingleFormulaCell(2)) ' value of 2 ' create a row in section1 ' the first cell references cell A from section1 ' the second cell references cell C from section2 section1.Rows = New NFormulaRowCollection row = New NFormulaRow section1.Rows.Add(row) row.Add(New NSingleFormulaCell("A+2")) ' value of 7 row.Add(New NSingleFormulaCell("C+2")) ' value of 9 ' create the second section Dim section2 As New NFormulaSection("Section2", "Section2") sheet.Sections.Add(section2) ' create named cells in section1 ' cell C references cells A and B from section1 with their full names ' cell D references cell A1 from section1 section2.Cells = New NFormulaCellDictionary section2.Cells.Add("C", New NSingleFormulaCell("Section1.A+Section1.B")) ' value of 7 section2.Cells.Add("D", New NSingleFormulaCell("Section1.A1")) ' value of 7 ' create a row in section1 ' the first cell references cell A1 from section1 ' the second cell references cell B1 from section2 section2.Rows = New NFormulaRowCollection row = New NFormulaRow section2.Rows.Add(row) row.Add(New NSingleFormulaCell("Section1.A1+2")) ' value of 9 row.Add(New NSingleFormulaCell("Section1.B1+2")) ' value of 11

Visual Basic

Copy Code

Dim row As NFormulaRow

' create a new sheet 
Dim sheet As New NFormulaSheet

' create the first section
Dim section1 As New NFormulaSection("Section1", "Section1")
sheet.Sections.Add(section1)

' create named cells in section1
' they are both constant cells
section1.Cells = New NFormulaCellDictionary
section1.Cells.Add("A", New NSingleFormulaCell(5)) ' value of 5
section1.Cells.Add("B", New NSingleFormulaCell(2)) ' value of 2

' create a row in section1 
' the first cell references cell A from section1
' the second cell references cell C from section2
section1.Rows = New NFormulaRowCollection
row = New NFormulaRow
section1.Rows.Add(row)

row.Add(New NSingleFormulaCell("A+2")) ' value of 7
row.Add(New NSingleFormulaCell("C+2")) ' value of 9

' create the second section
Dim section2 As New NFormulaSection("Section2", "Section2")
sheet.Sections.Add(section2)

' create named cells in section1
' cell C references cells A and B from section1 with their full names
' cell D references cell A1 from section1
section2.Cells = New NFormulaCellDictionary
section2.Cells.Add("C", New NSingleFormulaCell("Section1.A+Section1.B")) ' value of 7 
section2.Cells.Add("D", New NSingleFormulaCell("Section1.A1")) ' value of 7

' create a row in section1 
' the first cell references cell A1 from section1
' the second cell references cell B1 from section2
section2.Rows = New NFormulaRowCollection
row = New NFormulaRow
section2.Rows.Add(row)

row.Add(New NSingleFormulaCell("Section1.A1+2")) ' value of 9
row.Add(New NSingleFormulaCell("Section1.B1+2")) ' value of 11