How to Build Dependent Lists using Excel Dynamic Functions in C# .NET
Tags: webdev, devops, csharp, tutorial
Published: 2024-02-05 21:46:09

---
canonical_url: https://developer.mescius.com/blogs/how-to-build-dependent-lists-using-excel-dynamic-functions-in-c-sharp-net
description: Learn how to build dependent lists using Excel dynamic functions in C# .NET. See more from Document Solutions today.
---
**What You Will Need**
- Visual Studio
- .NET 6+
- NuGet Package:
[DS.Documents.Excel](https://www.nuget.org/packages/DS.Documents.Excel)

**Controls Referenced**
- [Document Solutions for Excel, .NET Edition](https://developer.mescius.com/document-solutions/dot-net-excel-api/)
- [Documentation](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/overview.html) | [Online Demo Explorer](https://developer.mescius.com/document-solutions/dot-net-excel-api/demos/)

**Tutorial Concept**
Smart dependent lists using Excel functions with a C#/.NET Excel API - Dynamic or smart dependent lists are often used in Excel reports, and this process will add functionality to a desktop application.

---

In Microsoft Excel, a Dependent List or Cascading Dropdown signifies two or more lists where the items of one list change depending on another list. Dependent lists often find their use in Excel-based business reports, such as class-students lists in academic scorecards, region-country lists in regional sales reports, year-region lists in population dashboards, and unit-line-product lists in a production summary report, to name a few.

In this blog, you will learn how to programmatically create master and dependent dropdown lists with Excel’s Data Validation and Dynamic array functions UNIQUE, CHOOSECOLS, and FILTER using [<span>Document Solutions for Excel (DsExcel) for C# .NET</span>](https://developer.mescius.com/document-solutions/dot-net-excel-api/ "/document-solutions/dot-net-excel-api/").

## Use case

Suppose you are preparing an Excel report to study, compare, and analyze the behavior of customer orders. You get the customer’s order history from the sales department in the format as shown below:

![C# Excel Functions](//cdn.mescius.io/umb/media/lsejtq4g/01-excel.png?rmode=max&width=750&height=385)

In your Excel report, you want to show the details of a particular customer’s order. To avoid invalid data from being looked upon in the report, you wanted to add two dropdowns, one for the Customer Name and the other for the Order ID. The values for these dropdowns should come from the order history data shown above. However, in the Order ID dropdown, you want to display the values related to the selected customer only, as illustrated below:

![C# Excel Functions](//cdn.mescius.io/umb/media/1tjhv5w3/02-orderdependent.png?rmode=max&width=732&height=398)

### Design Approach in Excel

One can create a smart, interactive dependent list using several different ways in MS Excel; for example,

*   using a [<span>Data Validation List</span>](https://support.microsoft.com/en-us/office/apply-data-validation-to-cells-29fecbcc-d1b9-42c1-9d76-eff3ce5f7249#:~:text=Add%20data%20validation%20to%20a%20cell%20or%20a%20range&text=Select%20one%20or%20more%20cells,list%20values%2C%20separated%20by%20commas. "https://support.microsoft.com/en-us/office/apply-data-validation-to-cells-29fecbcc-d1b9-42c1-9d76-eff3ce5f7249#:~:text=Add%20data%20validation%20to%20a%20cell%20or%20a%20range&text=Select%20one%20or%20more%20cells,list%20values%2C%20separated%20by%20comma") in combination with regular built-in functions such as OFFSET, INDEX, MATCH, or dynamic array functions such as FILTER, UNIQUE, etc.
*   using Form controls with linked cells
*   using VBA, and so on.

In this blog, we will use the approach of a **Data Validation List** with **dynamic array functions** to create the dependent list described in the use case above.

In Excel, you can use the _List_ option with the **Data Validation** feature to create the dropdowns as shown below:

![C# Excel Functions](//cdn.mescius.io/umb/media/24oa2yyz/03-dataval.png?rmode=max&width=742&height=366)

However, dynamic array functions like UNIQUE, FILTERS, and others could not be used as a data validation source because of their spilling behavior. While the dynamic array functions return an array, a list-based data validation must refer to an actual range within the worksheet or a hard-coded comma-separated list. As a result, these functions must be evaluated separately in the worksheet, and the reference of the evaluating cell must be used as the source for the lists.

### Programmatic implementation using DsExcel

With [<span>DsExcel</span>](https://developer.mescius.com/document-solutions/dot-net-excel-api/ "/document-solutions/dot-net-excel-api/"), developers have access to an interface-based API modeled on Excel's document object model, offering a complete suite of tools to easily create, manipulate, convert, and share Microsoft Excel-compatible spreadsheets. It empowers you to manage your data easily and efficiently and build solutions tailored to your unique requirements.

Let’s walk through the steps to create the desired master (Customer Name) and dependent (OrderID) dropdown lists programmatically using DsExcel.

Check out the documentation to see [<span>how to get started with DsExcel</span>](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/getting-started.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/getting-started.html") in your C# application.

#### **Step 1 - Workbook Initialization**

Using DsExcel API, the first step is to initialize an instance of Workbook. You can then choose to either open an existing Excel document or create a new workbook as per your business needs. For this blog, we’re loading an existing Excel document with the customer’s order history using the [_<span>Open</span>_](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IWorkbook~Open.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IWorkbook~Open.html") method with the [**IWorkbook**](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IWorkbook.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IWorkbook.html") interface as depicted below:

```
    Workbook workbook = new Workbook();
    workbook.Open("CustomerOrderHistory.xlsx");
```

#### **Step 2 - Get the Worksheet**

Next, you need to get the worksheet for creating the required report. With DsExcel, you can get the worksheet using the [_<span>Worksheets</span>_](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IWorkbook~Worksheets.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IWorkbook~Worksheets.html") collections from the **IWorkbook** interface. You can also opt to [create a new worksheet](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/WorkWithSheets.html#i-heading-add-multiple-worksheets "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/WorkWithSheets.html#i-heading-add-multiple-worksheets"). However, for the simplicity of the formulas to be used in the report, we are creating the report on the same worksheet that stores the order’s history as depicted below:

```
    IWorksheet worksheet;
    worksheet = workbook.Worksheets["data"]; //OR workbook.Worksheets[0];
```

_Note: The report's layout and other required configurations have already been created in the Excel file because they are outside the scope of this blog. The report starts at the location $L$2 as shown below:_

![C# Excel Functions](//cdn.mescius.io/umb/media/4u1cisfx/04-excel.png?rmode=max&width=716&height=374)

#### **Step 3 - Get the unique list of customer names (for master dropdown)**

After the initialization, you need to get the list of unique customer names for the master dropdown to be added to the “Select Customer Name” section in the report. For this, choose any cell in the worksheet with space at the bottom to spill the data vertically; we used cell T3\. Next, use the UNIQUE function on the required Customer Name data range. 

With DsExcel, you can get a cell or range of cells using the [_<span>Range</span>_](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IWorksheet~Range.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IWorksheet~Range.html") property with the [**IWorksheet**](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IWorksheet.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IWorksheet.html") interface and set a dynamic formula to it using the [_Formula2_](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IRange~Formula2.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IRange~Formula2.html") property of the [**IRange**](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IRange.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IRange.html") interface as depicted below:

```
    IRange rngUniqueCustomerNames;
    rngUniqueCustomerNames = worksheet.Range["T3"]; //dummy cell to get unique list of customer names
    rngUniqueCustomerNames.Formula2 = "=UNIQUE($B$2:$B$2156)";
```

#### **Step 4 - Create the master dropdown**

Once you have the list of customer names, use it as a source for the master dropdown created using the **Data Validation** on **List**. In this blog sample, this master dropdown is created in cell L3.

Using DsExcel, data validation is configured on a range using the [_<span>Validation</span>_](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IRange~Validation.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IRange~Validation.html") property of the **IRange** interface. Add a new validation rule instance for a range using the [_Add_](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IValidation~Add.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IValidation~Add.html") method of the [**IValidation**](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IValidation.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IValidation.html") interface. Choose the ValidationType.List option for the List type data validation and set the formula to the cell with the UNIQUE formula; here it is T3 as depicted below:

```
    IValidation listValidation = worksheet.Range["L3"].Validation;
    listValidation.Add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Equal,"=$T$3#");
```

Note that to get the resultant range of the dynamic array function, the cell reference is followed by a **#**

#### **Step 5 - Fetch the list of unique OrderIDs (for dependent dropdown)**

After you have the master dropdown ready, let’s get the list of unique OrderIDs for the customer name selected in the master dropdown. To do this, again select any cell in the worksheet (in this sample, this cell is $V$2). Use the following formula in this cell to get the desired list of OrderIDs.

```
    =CHOOSECOLS(FILTER(Unique_Cus_Order_combo, 
    CHOOSECOLS(Unique_Cus_Order_combo,2)=CustomerName), 1).
```

The breakdown of the formula is as follows:

*   Defined nameCustomerName refers to the value of the cell containing the master dropdown; in this sample, it refers to =$L$3

![C# Excel Functions](//cdn.mescius.io/umb/media/iuvb0vmj/05-excel.png?rmode=max&width=751&height=415)

*   Defined nameUnique_Cus_Order_combo refers to the range of unique combinations of order ID and customer name. It stores the formula =UNIQUE(data!$A$2:$B$2156) where range A and B contain the OrderID and Customer Names, respectively. The data it returns is as shown below:

![C# Excel Functions](//cdn.mescius.io/umb/media/hrmnnay4/06-excel.png)

*   The inner CHOOSECOLS function gives the list of Customer names from the range represented by Unique_Cus_Order_combo to match against the CustomerName in the FILTER function.

![C# Excel Functions](//cdn.mescius.io/umb/media/tynfhebg/07-choosecol.png?rmode=max&width=756&height=114)

*   The FILTER function filters out the data from Unique_Cus_Order_combo corresponding to the selected customer name, as shown below:

![C# Excel Functions](//cdn.mescius.io/umb/media/ancblr4p/08-excel.png)

*   Finally, the outer CHOOSECOLS function returns the desired list of OrderIDs from the filtered range, as shown below:

![C# Excel Functions](//cdn.mescius.io/umb/media/jf5px3tx/09-excel.png)

To set the defined names and dynamic formula using DsExcel, follow the sample code below:

```
     workbook.Names.Add("CustomerName", "=$L$3");
     workbook.Names.Add("Unique_Cus_Order_combo", "=UNIQUE(data!$A$2:$B$2156)");

    IRange rngUniqueOrderIds;
    rngUniqueOrderIds = worksheet.Range["V2"]; //dummy range to get unique list of customer names
    rngUniqueOrderIds.Formula2 = "=CHOOSECOLS(FILTER(Unique_Cus_Order_combo, CHOOSECOLS(Unique_Cus_Order_combo,2)=CustomerName), 1)";
```

#### **Step 6 - Populate the dependent dropdown**

The next step is to populate the OrderID dropdown (in this sample, it is at L6) using the list fetched in the previous step. For this, add a data validation of the type list (same as the one added for the master dropdown) and set its source value to the cell value containing the formula in the previous step (i.e., =$V$2)prefixed with a **#.**

```
    IValidation orderIdList = worksheet.Range["L6"].Validation;
    orderIdList.Add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Equal, "=$v$2#");
```

#### **Step 7 - Set the default values to dropdown and save the workbook**

Finally, set the default values to the dropdowns using the [_<span>Value</span>_](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IRange~Value.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IRange~Value.html") property of the **IRange** interface and save the workbook using the [_Save_](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IWorkbook~Save.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IWorkbook~Save.html") method of the **IWorkbook** interface as depicted in the code snippet below:

```
    worksheet.Range["L3"].Value = "Paul Henriot";
    worksheet.Range["L6"].Value = 10248;

    workbook.Save("CustomerOrderHistoryReport.xlsx");
```

The generated Excel file with the smart dependent lists appears as illustrated in the gif below:

![C# Excel Functions](//cdn.mescius.io/umb/media/zmwf0qi0/10-drpdown.gif)

Download the [<span>complete sample</span>](https://cdn.mescius.io/umb/media/njilunbo/smartdependentlist.zip)<span>.</span><span></span>

## Conclusion

In conclusion, Excel's diverse array of strategies for crafting smart dependent lists is further complemented by the invaluable support provided by Document Solutions for Excel API (DsExcel). This powerful tool offers flawless Excel compatibility, extending over 450 built-in Excel functions. Leveraging this capability, users can effortlessly generate dependent lists and execute complex spreadsheet calculations, all without encountering any hassles in C#.