How to Build Dependent Lists using Excel Dynamic Functions in C# .NET

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 Document Solutions for Excel (DsExcel) for C# .NET.

Want to Learn More? Try out Document Solution for 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:

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:

Design Approach in Excel

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

• using a Data Validation List 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:

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 DsExcel, 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 how to get started with DsExcel 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 Open method with the IWorkbook 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 Worksheets collections from the IWorkbook interface. You can also opt to create a new worksheet. 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:

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 Range property with the IWorksheet interface and set a dynamic formula to it using the Formula2 property of the IRange 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 Validation property of the IRange interface. Add a new validation rule instance for a range using the Add method of the IValidation 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

• 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:

• 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.

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

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

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 Value property of the IRange interface and save the workbook using the Save 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:

Download the complete sample.

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#.

Want to Learn More? Try out Document Solution for Excel API!