Precedents and Dependents | Document Solutions for Excel, .NET Edition

Precedents and Dependents

In This Topic

Sometimes, in worksheets containing lots of formulas, it becomes difficult to identify which cell values or ranges are taken into consideration while doing calculations or how the result is calculated. Also, which cells are impacted if a cell value is modified. Hence, comes the need for precedent and dependent cells or ranges. DsExcel library provides GetPrecedents and GetDependents methods in the IRange interface, which help in identifying the precedent and dependent cells or ranges in excel worksheets.

Precedents: Cells or ranges which are directly or indirectly referred to, by the formulas in other cells
Dependents: Cells or ranges which contain formulas that refer to other cells directly or indirectly

For example, the value in cell A1 =10, A2 = 20 and B1 = Sum (A1+A2), then A1 and A2 are the precedent cells of B1 which are used for calculating the value of B1. Also, B1 is the dependent cell for A1 and A2 whose value is calculated based on values of cell A1 and A2.

Direct Precedents

Refer to the following example code to get the direct precedent ranges in a worksheet.

C#	Copy Code
public void DirectPrecedents() { // Initialize workbook Workbook workbook = new Workbook(); // Fetch default worksheet IWorksheet worksheet = workbook.Worksheets[0]; // Set Formula in Cell E2 worksheet.Range["E2"].Formula = "=sum(A1:A2, B4,C1:C3)"; // Set Value of Cells worksheet.Range["A1"].Value = 1; worksheet.Range["A2"].Value = 2; worksheet.Range["B4"].Value = 3; worksheet.Range["C1"].Value = 4; worksheet.Range["C2"].Value = 5; worksheet.Range["C3"].Value = 6; // Get Precedent cells of Range E2 foreach (var item in worksheet.Range["E2"].GetPrecedents()) { item.Interior.Color = Color.Pink; } // Saving workbook to Xlsx workbook.Save(@"Precedents.xlsx", SaveFileFormat.Xlsx); }

Copy Code

public void DirectPrecedents()
{
    // Initialize workbook
    Workbook workbook = new Workbook();
    // Fetch default worksheet 
    IWorksheet worksheet = workbook.Worksheets[0];

    // Set Formula in Cell E2
    worksheet.Range["E2"].Formula = "=sum(A1:A2, B4,C1:C3)";
    // Set Value of Cells
    worksheet.Range["A1"].Value = 1;
    worksheet.Range["A2"].Value = 2;
    worksheet.Range["B4"].Value = 3;
    worksheet.Range["C1"].Value = 4;
    worksheet.Range["C2"].Value = 5;
    worksheet.Range["C3"].Value = 6;

    // Get Precedent cells of Range E2
    foreach (var item in worksheet.Range["E2"].GetPrecedents())
    {
        item.Interior.Color = Color.Pink;
    }

    // Saving workbook to Xlsx
    workbook.Save(@"Precedents.xlsx", SaveFileFormat.Xlsx);

}

The below image shows the direct precedent ranges (highlighted in pink).

Precedent range

Direct Dependents

Refer to the following example code to get direct dependent ranges in a worksheet.

C#	Copy Code
public void DirectDependents() { // Initialize workbook Workbook workbook = new Workbook(); // Fetch default worksheet IWorksheet worksheet = workbook.Worksheets[0]; // Set Value of Cell A1 worksheet.Range["A1"].Value = 100; // Set Formula in Cell C1 worksheet.Range["C1"].Formula = "=$A$1"; // Set Formula in Range E1:E5 worksheet.Range["E1:E5"].Formula = "=$A$1"; // Get Dependent cells of Range A1 foreach (var item in worksheet.Range["A1"].GetDependents()) { item.Interior.Color = Color.LightGreen; } // Saving workbook to Xlsx workbook.Save(@"Dependents.xlsx", SaveFileFormat.Xlsx); }

Copy Code

public void DirectDependents()
{
    // Initialize workbook
    Workbook workbook = new Workbook();
    // Fetch default worksheet 
    IWorksheet worksheet = workbook.Worksheets[0];

    // Set Value of Cell A1
    worksheet.Range["A1"].Value = 100;
    // Set Formula in Cell C1
    worksheet.Range["C1"].Formula = "=$A$1";
    // Set Formula in Range E1:E5
    worksheet.Range["E1:E5"].Formula = "=$A$1";

    // Get Dependent cells of Range A1
    foreach (var item in worksheet.Range["A1"].GetDependents())
    {
        item.Interior.Color = Color.LightGreen;
    }

    // Saving workbook to Xlsx
    workbook.Save(@"Dependents.xlsx", SaveFileFormat.Xlsx);
}

The below image shows the dependent ranges (highlighted in green).

Dependent range

Direct and Indirect Precedents

You can also identify the direct and indirect precedents by using the overloaded GetPrecedents method which provide the includeIndirect parameter. This parameter when set to true returns all the direct and indirect precedents. However, its default value is false which returns only direct precedents.

Refer to the following example code to get all the precedent ranges in a worksheet.

C#	Copy Code
public void DirectIndirectPrecedents() { //create a new workbook var workbook = new Workbook(); IWorksheet worksheet0 = workbook.Worksheets[0]; // Set Formula in Cells worksheet0.Range["E2"].Formula = "=Sum(C1:C2)"; worksheet0.Range["C1"].Formula = "=B1"; worksheet0.Range["B1"].Formula = "=Sum(A1:A2)"; // Set Value of Cells worksheet0.Range["A1"].Value = 1; worksheet0.Range["A2"].Value = 2; worksheet0.Range["C2"].Value = 3; List<IRange> list = new List<IRange>(); foreach (var item in worksheet0.Range["E2"].GetPrecedents(true)) { item.Interior.Color = Color.Red; } //save to an excel file workbook.Save("getAllPrecedents.xlsx"); }

Copy Code

public void DirectIndirectPrecedents()
{
   //create a new workbook
        var workbook = new Workbook();

        IWorksheet worksheet0 = workbook.Worksheets[0];
        // Set Formula in Cells
        worksheet0.Range["E2"].Formula = "=Sum(C1:C2)";
        worksheet0.Range["C1"].Formula = "=B1";
        worksheet0.Range["B1"].Formula = "=Sum(A1:A2)";
        // Set Value of Cells
        worksheet0.Range["A1"].Value = 1;
        worksheet0.Range["A2"].Value = 2;
        worksheet0.Range["C2"].Value = 3;

        List<IRange> list = new List<IRange>();
        foreach (var item in worksheet0.Range["E2"].GetPrecedents(true))
        {
            item.Interior.Color = Color.Red;
        }

        //save to an excel file
        workbook.Save("getAllPrecedents.xlsx");
}

The below image shows all the precedent ranges of cell E2.

Precedent ranges

Direct and Indirect Dependents

You can also identify the direct and indirect dependentsby using the overloaded GetDependents method which provide the includeIndirect parameter. This parameter when set to true returns all the direct and indirect dependents. However, its default value is false which returns only direct dependents.

Refer to the following example code to get all the dependent ranges in a worksheet.

C#	Copy Code
public void DirectIndirectDependents() { //create a new workbook var workbook = new Workbook(); worksheet1.Range["C1"].Formula = "A1"; worksheet1.Range["C2"].Formula = "A1"; worksheet1.Range["D1"].Formula = "A1"; worksheet1.Range["F1"].Formula = "A1"; worksheet1.Range["G1"].Formula = "A1"; worksheet1.Range["E1"].Formula = "D1"; // Set Value of Cells worksheet1.Range["B2"].Value = 1; worksheet1.Range["B3"].Value = 2; foreach (var item in worksheet1.Range["A1"].GetDependents(true)) { item.Interior.Color = Color.LightGreen; } //save to an excel file workbook.Save("getAllDependents.xlsx"); }

Copy Code

public void DirectIndirectDependents()
{
   //create a new workbook
    var workbook = new Workbook();

    worksheet1.Range["C1"].Formula = "A1";
    worksheet1.Range["C2"].Formula = "A1";
    worksheet1.Range["D1"].Formula = "A1";
    worksheet1.Range["F1"].Formula = "A1";
    worksheet1.Range["G1"].Formula = "A1";
    worksheet1.Range["E1"].Formula = "D1";
    // Set Value of Cells
    worksheet1.Range["B2"].Value = 1;
    worksheet1.Range["B3"].Value = 2;

    foreach (var item in worksheet1.Range["A1"].GetDependents(true))
    {
        item.Interior.Color = Color.LightGreen;
    }
    //save to an excel file
    workbook.Save("getAllDependents.xlsx");
}

The below image shows all the dependent ranges of cell A1.

Precedent ranges