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