Spread Windows Forms 17
Spread Windows Forms 17.0 Product Documentation / Developer's Guide / Formulas in Cells / Precedents and Dependents
In This Topic
    Precedents and Dependents
    In This Topic

    Spread Winforms allows you to trace precedents and dependents cells in spreadsheets. 

    Tracing precedents and dependents cells help you to check calculation issues, debug formulas, and validate the accuracy of results. You can also display cells related to the selected formula cell and observe which cells are impacted if a cell value is modified.

    Trace Precedents

    You can trace the direct precedents of a cell by using ShowPrecedents method of the IRange interface. It displays the relational arrows towards the precedents as shown in the below gif:

    You can also use the DirectPrecedents property of IRange interface to fetch a range object that represents the range containing all the direct precedents of a cell.

    The following example shows the implementation of ShowPrecedents method and DirectPrecedents property.

    C#
    Copy Code 
    // Set Formula in Cell E2
fpSpread1.Sheets[0].Cells["E2"].Formula = "Sum(C1:C2, C5)";
// Set Formula in Cell C1
fpSpread1.Sheets[0].Cells["C1"].Formula = "B1";
// Set Formula in Cell B1
fpSpread1.Sheets[0].Cells["B1"].Formula = "Sum(A1:A2)";
// Set Value of Cells
fpSpread1.Sheets[0].Cells["A1"].Value = 1;
fpSpread1.Sheets[0].Cells["A2"].Value = 2;
fpSpread1.Sheets[0].Cells["C2"].Value = 3;
fpSpread1.Sheets[0].Cells["C5"].Value = 4;
// ShowPrecedents of Cell E2
fpSpread1.ShowPrecedents(0, 1, 4);
// DirectPrecedents of Cell E2
IRange DirectPrecedents = fpSpread1.AsWorkbook().Worksheets[0].Cells["E2"].DirectPrecedents;
listBox1.Items.Add("DirectPrecedents of Cell E2 are :");
int areas1 = DirectPrecedents.Areas.Count;
for (int i = 0; i < areas1; i++)
{
 listBox1.Items.Add(DirectPrecedents.Areas[i].ToString());
}
// Output
// DirectPrecedents of Cell E2 :
// [fpSpread1]Sheet1!$C$1:$C$2
// [fpSpread1]Sheet1!$C$5
    VB
    Copy Code 
    'Set Formula in Cell E2
FpSpread1.Sheets(0).Cells("E2").Formula = "Sum(C1:C2, C5)"
'Set Formula in Cell C1
FpSpread1.Sheets(0).Cells("C1").Formula = "B1"
'Set Formula in Cell B1
FpSpread1.Sheets(0).Cells("B1").Formula = "Sum(A1:A2)"
'Set Value of Cells
FpSpread1.Sheets(0).Cells("A1").Value = 1
FpSpread1.Sheets(0).Cells("A2").Value = 2
FpSpread1.Sheets(0).Cells("C2").Value = 3
FpSpread1.Sheets(0).Cells("C5").Value = 4
'ShowPrecedents of Cell E2
FpSpread1.ShowPrecedents(0, 1, 4)
'DirectPrecedents of Cell E2
Dim DirectPrecedents As IRange = FpSpread1.AsWorkbook().Worksheets(0).Cells("E2").DirectPrecedents
listBox1.Items.Add("DirectPrecedents of Cell E2 are :")
Dim areas1 As Integer = DirectPrecedents.Areas.Count
For i As Integer = 0 To areas1 - 1
    listBox1.Items.Add(DirectPrecedents.Areas(i).ToString())
Next
'Output
'DirectPrecedents of Cell E2 :
'[fpSpread1]Sheet1!$C$1$C$2
'[fpSpread1]Sheet1!$C$5

    The GetFullPrecedents method of IRange interface can be used to display all the precedents including external workbook references for the selected cell. The below gif demonstrates the tracing of precedents including external workbook references.


     

    Alternatively, the Precedents property of IRange interface can be used to return a range object that represents all the precedents of a cell. However, this property does not trace external references.

    The following example code shows the implementation of GetFullPrecedents method and Precedents property.

    C#
    Copy Code 
    // set value
fpSpread1.Sheets[0].Cells[1, 1].Value = "56";
fpSpread1.Sheets[0].SetValue(3, 2, 11);
fpSpread2.Sheets[0].SetValue(2, 2, 10);
fpSpread2.Sheets[0].SetValue(3, 2, 20);
fpSpread1.Sheets[0].Cells[0, 0].Formula = "C4";
// Assigning external cell reference formula in workbook2 referring to cell in workbook1
fpSpread2.Sheets[0].Cells[1, 1].Formula = "[Book1]Sheet1!B2";
fpSpread2.Sheets[0].SetFormula(1, 3, "SUM([Book1]Sheet1!C3:C4)");
fpSpread1.Sheets[0].SetFormula(2, 2, "Sum([Book2]Sheet1!C3:C4)");
// cross worksheet formula
fpSpread1.Sheets[1].Cells[0, 0].Formula = "Sheet1!B2";

// cross workbook referencing
// GetFullPrecedents of cell C3 of fpSpread1.Sheets[0]
IRange[] FullPrecedents = fpSpread1.AsWorkbook().Worksheets[0].Cells["C3"].GetFullPrecedents();
//MessageBox.Show(FullPrecedents.ToString());
listBox1.Items.Add("FullPrecedents of Cell C3 of fpSpread1.Sheets[0] are : ");
listBox1.Items.Add(FullPrecedents.GetValue(0).ToString());

// cross worksheet referencing in same workbook
// GetFullPrecedents of cell B2 of fpSpread2.Sheets[0]
listBox1.Items.Add("FullPrecedents of Cell B2 of fpSpread2.Sheets[0] are : ");
IRange[] FullPrecedents1 = fpSpread2.AsWorkbook().Worksheets[0].Cells["B2"].GetFullPrecedents();
listBox1.Items.Add(FullPrecedents1.GetValue(0).ToString());
// Precedents of Cell A1
listBox1.Items.Add("Precedents of Cell A1 : " + fpSpread1.AsWorkbook().Worksheets[0].Cells["A1"].Precedents[0]);
// Output
// Precedents of Cell A1 : [Book1]Sheet1!$C$4
    VB
    Copy Code 
    'set value
FpSpread1.Sheets(0).Cells(1, 1).Value = "56"
FpSpread1.Sheets(0).SetValue(3, 2, 11)
FpSpread2.Sheets(0).SetValue(2, 2, 10)
FpSpread2.Sheets(0).SetValue(3, 2, 20)
FpSpread1.Sheets(0).Cells(0, 0).Formula = "C4"
'Assigning external cell reference formula in workbook2 referring to cell in workbook1
FpSpread2.Sheets(0).Cells(1, 1).Formula = "[Book1]Sheet1!B2"
FpSpread2.Sheets(0).SetFormula(1, 3, "SUM([Book1]Sheet1!C3:C4)")
FpSpread1.Sheets(0).SetFormula(2, 2, "Sum([Book2]Sheet1!C3:C4)")
'cross worksheet formula
FpSpread1.Sheets(1).Cells(0, 0).Formula = "Sheet1!B2"
'cross workbook referencing
'GetFullPrecedents of cell C3 of fpSpread1.Sheets(0)
Dim FullPrecedents As IRange() = FpSpread1.AsWorkbook().Worksheets(0).Cells("C3").GetFullPrecedents(False)
'MessageBox.Show(FullPrecedents.ToString());
ListBox1.Items.Add("FullPrecedents of Cell C3 of fpSpread1.Sheets(0) are : ")
ListBox1.Items.Add(FullPrecedents.GetValue(0).ToString())
'cross worksheet referencing in same workbook
'GetFullPrecedents of cell B2 of fpSpread2.Sheets(0)
ListBox1.Items.Add("FullPrecedents of Cell B2 of fpSpread2.Sheets(0) are : ")
Dim FullPrecedents1 As IRange() = FpSpread2.AsWorkbook().Worksheets(0).Cells("B2").GetFullPrecedents()
ListBox1.Items.Add(FullPrecedents1.GetValue(0).ToString())
'Precedents of Cell A1
ListBox1.Items.Add("Precedents of Cell A1 : ")
ListBox1.Items.Add(FpSpread1.AsWorkbook().Worksheets(0).Cells("A1").Precedents(0))
'Output
'Precedents of Cell A1 : 
'[Book2]Sheet1!$C$4

    The GetFullPrecedents method also provides excludeUnlinkedWorksheet parameter which excludes all precedents referring to the workbook that are not loaded in the WorkbookSet. Its default value is true. However, it includes precedents from external worksheets in other workbooks that are not currently loaded in the WorkbookSet, when set to false.

    C#
    Copy Code 
    // Set external Formula in Cell E2 of fpSpread1 i.e. the formula include reference to fpSpread2
fpSpread1.Sheets[0].Cells["A1"].Value = 2000;
fpSpread1.Sheets[0].Cells["E2"].Formula = "A1 + [fpSpread2]Sheet1!A1";
// set value in fpSpread2 Cell A1
fpSpread2.Sheets[0].Cells["A1"].Value = 1000;
// Add two spread controls in a workbook set
fpSpread1.AsWorkbook().WorkbookSet.Workbooks.Add(fpSpread2.AsWorkbook());
// Unlink fpSpread2 from workbook set
fpSpread2.AsWorkbook().Close();
// Getting precedents by excluding the unlinked worksheet
listBox1.Items.Add("GetFullPrecedents of Cell E2 WITHOUT broken link :");
IRange[] precedents = fpSpread1.AsWorkbook().Worksheets[0].Cells["E2"].GetFullPrecedents(true);
// Output
// GetFullPrecedents of Cell E2 WITHOUT broken link :
// [fpSpread1]Sheet1!$A$1
// Getting precedents by including the unlinked worksheet
listBox1.Items.Add("GetFullPrecedents of Cell E2 WITH broken link - ExternalReference item(s) may be listed :");
IRange[] precedents2 = fpSpread1.AsWorkbook().Worksheets[0].Cells["E2"].GetFullPrecedents(false);
// Output
// GetFullPrecedents of Cell E2 WITH broken link - ExternalReference item(s) may be listed :
// GrapeCity.Spreadsheet.API.ExternalRange
// [fpSpread1]Sheet1!$A$1
    VB
    Copy Code 
    'Set external Formula in Cell E2 of fpSpread1 i.e. the formula include reference to fpSpread2
FpSpread1.Sheets(0).Cells("A1").Value = 2000
FpSpread1.Sheets(0).Cells("E2").Formula = "A1 + [fpSpread2]Sheet1!A1"
'set value in fpSpread2 Cell A1
FpSpread2.Sheets(0).Cells("A1").Value = 1000
'Set two spread controls in a workbook set
FpSpread1.AsWorkbook().WorkbookSet.Workbooks.Add(FpSpread2.AsWorkbook())
'Unlink fpSpread2 from workbook set
FpSpread2.AsWorkbook().Close()
'Getting precedents by excluding the unlinked worksheet
ListBox1.Items.Add("GetFullPrecedents of Cell E2 WITHOUT broken link :")
Dim precedents As IRange() = FpSpread1.AsWorkbook().Worksheets(0).Cells("E2").GetFullPrecedents(True)
'Output
'GetFullPrecedents of Cell E2 WITHOUT broken link :
'[fpSpread1]Sheet1!$A$1
'Getting precedents by including the unlinked worksheet
ListBox1.Items.Add("GetFullPrecedents of Cell E2 WITH broken link - ExternalReference item(s) may be listed :")
Dim precedents2 As IRange() = FpSpread1.AsWorkbook().Worksheets(0).Cells("E2").GetFullPrecedents(False)
'Output
'GetFullPrecedents of Cell E2 WITH broken link - ExternalReference item(s) may be listed :
'GrapeCity.Spreadsheet.API.ExternalRange
'[fpSpread1]Sheet1!$A$1

    Trace Dependents

    You can trace the direct dependents of a cell by using ShowDependents method of the IRange interface. It displays the relational arrows towards the dependents as shown in the below gif:


    You can also use the DirectDependents property of IRange interface to fetch a range object that represents the range containing all the direct dependents of a cell.

    The following example shows the implementation of ShowDependents method and DirectDependents property.

    C#
    Copy Code 
    // Set formulas
fpSpread1.Sheets[1].Cells[0, 0].Formula = "B2+B3";
fpSpread1.Sheets[1].Cells["C1"].Formula = "A1";
fpSpread1.Sheets[1].Cells["C2"].Formula = "A1";
fpSpread1.Sheets[1].Cells["D1"].Formula = "A1";
fpSpread1.Sheets[1].Cells["F1"].Formula = "A1";
fpSpread1.Sheets[1].Cells["G1"].Formula = "A1";
fpSpread1.Sheets[1].Cells["E1"].Formula = "D1";
// Set Value of Cells
fpSpread1.Sheets[1].Cells["B2"].Value = 1;
fpSpread1.Sheets[1].Cells["B3"].Value = 2;
// ShowDependents of Cell A1
fpSpread1.ShowDependents(1, 0, 0);
// DirectDependents of Cell A1
IRange DirectDependents = fpSpread1.AsWorkbook().Worksheets[1].Cells["A1"].DirectDependents;
listBox1.Items.Add("DirectDependents of Cell A1 are :");
int areas1 = DirectDependents.Areas.Count;
for (int i = 0; i < areas1; i++)
{
 listBox1.Items.Add(DirectDependents.Areas[i].ToString());
}
// Output
// DirectDependents of Cell A1 are :
// [fpSpread1]Sheet2!$C$1:$C$2
// [fpSpread1]Sheet2!$D$1
// [fpSpread1]Sheet2!$F$1:$G$1
    VB
    Copy Code 
    'Set Formula in Cells
FpSpread1.Sheets(1).Cells(0, 0).Formula = "B2+B3"
FpSpread1.Sheets(1).Cells("C1").Formula = "A1"
FpSpread1.Sheets(1).Cells("C2").Formula = "A1"
FpSpread1.Sheets(1).Cells("D1").Formula = "A1"
FpSpread1.Sheets(1).Cells("F1").Formula = "A1"
FpSpread1.Sheets(1).Cells("G1").Formula = "A1"
FpSpread1.Sheets(1).Cells("E1").Formula = "D1"
'Set Value of Cells
FpSpread1.Sheets(1).Cells("B2").Value = 1
FpSpread1.Sheets(1).Cells("B3").Value = 2
'ShowDependents of Cell A1
FpSpread1.ShowDependents(1, 0, 0)
'DirectDependents of Cell A1
Dim DirectDependents As IRange = FpSpread1.AsWorkbook().Worksheets(1).Cells("A1").DirectDependents
ListBox1.Items.Add("DirectDependents of Cell A1 are :")
Dim areas1 As Integer = DirectDependents.Areas.Count
For i As Integer = 0 To areas1 - 1
    ListBox1.Items.Add(DirectDependents.Areas(i).ToString())
Next
'Output
'DirectDependents of Cell A1 are :
'[fpSpread1]Sheet2!$C$1$C$2
'[fpSpread1]Sheet2!$D$1
'[fpSpread1]Sheet2!$F$1:$G$1

    The GetFullDependents method of IRange interface can be used to display all the dependents including external workbook references for the selected cell. The below gif demonstrates the tracing of dependents including external workbook references.


     

    Alternatively, the Dependents property of IRange interface can be used to return a range object that represents all the dependents of a cell. However, this property does not trace external references.

    The following example shows the implementation GetFullDependents method and Dependents property.

    C#
    Copy Code 
    // set value
fpSpread1.Sheets[0].Cells[1, 1].Value = "56";
fpSpread1.Sheets[0].SetValue(3, 2, 11);
fpSpread2.Sheets[0].SetValue(2, 2, 10);
fpSpread2.Sheets[0].SetValue(3, 2, 20);
fpSpread1.Sheets[0].Cells[0, 0].Formula = "C4";
// Assigning external cell reference formula in workbook2 referring to cell in workbook1
fpSpread2.Sheets[0].Cells[1, 1].Formula = "[Book1]Sheet1!B2";
fpSpread2.Sheets[0].SetFormula(1, 3, "SUM([Book1]Sheet1!C3:C4)");
fpSpread1.Sheets[0].SetFormula(2, 2, "Sum([Book2]Sheet1!C3:C4)");
// cross worksheet formula
fpSpread1.Sheets[1].Cells[0, 0].Formula = "Sheet1!B2";

// GetFullDependents of cell C4 of fpSpread2.Sheets[0]
listBox1.Items.Add("FullDependents of Cell C4 of fpSpread2.Sheets[0] are : ");
IRange[] FullDependents = fpSpread2.AsWorkbook().Worksheets[0].Cells["C4"].GetFullDependents();
listBox1.Items.Add(FullDependents.GetValue(0).ToString());
listBox1.Items.Add(FullDependents.GetValue(1).ToString());
// Dependents of Cell C4
IRange Dependents = fpSpread1.AsWorkbook().Worksheets[1].Cells["C4"].Dependents;
listBox1.Items.Add("Dependents of Cell C4 are :");
int areas = Dependents.Areas.Count;
for (int i = 0; i < areas; i++)
{
  listBox1.Items.Add(Dependents.Areas[i].ToString());
}
// Output
// Dependents of Cell C4 are :
// [Book1]Sheet1!$A$1
    VB
    Copy Code 
    'set value
FpSpread1.Sheets(0).Cells(1, 1).Value = "56"
FpSpread1.Sheets(0).SetValue(3, 2, 11)
FpSpread2.Sheets(0).SetValue(2, 2, 10)
FpSpread2.Sheets(0).SetValue(3, 2, 20)
FpSpread1.Sheets(0).Cells(0, 0).Formula = "C4"
'Assigning external cell reference formula in workbook2 referring to cell in workbook1
FpSpread2.Sheets(0).Cells(1, 1).Formula = "[Book1]Sheet1!B2"
FpSpread2.Sheets(0).SetFormula(1, 3, "SUM([Book1]Sheet1!C3:C4)")
FpSpread1.Sheets(0).SetFormula(2, 2, "Sum([Book2]Sheet1!C3:C4)")
'cross worksheet formula
FpSpread1.Sheets(1).Cells(0, 0).Formula = "Sheet1!B2"
'GetFullDependents of cell C4 of fpSpread2.Sheets(0)
ListBox1.Items.Add("FullDependents of Cell C4 of fpSpread2.Sheets(0) are : ")
Dim FullDependents As IRange() = FpSpread2.AsWorkbook().Worksheets(0).Cells("C4").GetFullDependents()
ListBox1.Items.Add(FullDependents.GetValue(0).ToString())
ListBox1.Items.Add(FullDependents.GetValue(1).ToString())
'Dependents of Cell C4
Dim Dependents As IRange = FpSpread1.AsWorkbook().Worksheets(1).Cells("C4").Dependents
ListBox1.Items.Add("Dependents of Cell C4 are :")
Dim areas1 As Integer = Dependents.Areas.Count
For i As Integer = 0 To areas1 - 1
    ListBox1.Items.Add(Dependents.Areas(i).ToString())
Next
'Output
'Dependents of Cell C4 are :
'[Book1]Sheet1!$A$1