Creating Cascading Drop-down Lists in Excel

If you’re using Microsoft Excel to capture and track data, one of the challenges is maintaining good data quality when more than one person is updating the workbook. The data validation features in Excel help by only allowing the user to select data based on pre-defined options in a list. This feature works well on individual cells. However, if you have a column that depends on the value in a different column, you will need to get a little more creative. This post describes the steps for creating cascading drop-down lists in Excel using a combination of data validation and named ranges.

Step 1 – Define the Reference Data

Let’s assume that we are collecting data that includes Organization and Department attributes. In this example, an Organization is the parent to one or more Departments (children). We’ll set up the reference data for the data validation drop-down lists as follows on its own worksheet.

  • Column A (“ORGANIZATIONS”) represents the list of valid Organizations to appear in the Organization drop-down list.
  • Column B (“ORGANIZATION TO DEPARTMENT MAPPINGS”) represents the names given to ranges that will be defined in a later step.
  • Columns D through E (“REF_ORGANIZATION_X_DEPARTMENTS”) represent the lists of valid Departments to appear in the Department drop-down list depending upon which Organization is selected.
Excel Cascading Drop-down List - Reference Data
Reference Data

Step 2 – Define the Named Ranges

Now that we have the reference data established, we can define the named ranges that refer to these lists. The named range entry defined as REF_ORGANIZATION_TO_DEPARTMENT_MAPPINGS is the key to creating the cascading drop-down list functionality. It is important that the values in Column B (above) match the names given to the REF_ORGANIZATION_X_DEPARTMENTS ranges. This value is used in a subsequent step using a combination of INDIRECT and VLOOKUP in the data validation formula.

Excel Cascading Drop-down List - Named Ranges
Named Ranges

Step 3 – Define the Organization Data Validation (Parent Column)

In this step, we establish the standard data validation on the parent data in Column A (“REF_ORGANIZATIONS”) on a new data collection worksheet.

Excel Cascading Drop-down List - Parent Level Validation
Parent Level Validation

Step 4 – Define the Department Data Validation (Child / Dependent Column)

Here we establish the data validation rule that performs the cascading drop-down list function. Once the user selects a valid Organization, this formula performs a VLOOKUP against the REF_ORGANIZATION_TO_DEPARTMENT_MAPPINGS named range and returns the name of the Department named range, e.g., REF_ORGANIZATION_X_DEPARTMENTS, associated with the selected Organization. The indirect function then converts that name, a text value, into the named range reference.

=INDIRECT(VLOOKUP(A2,REF_ORGANIZATION_TO_DEPARTMENT_MAPPINGS,2,FALSE))
Excel Cascading Drop-down List - Child Level Validation
Child Level Validation

When you click the OK button, Excel will display the following error message. Click the Yes button to continue.

Excel Cascading Drop-down List - Validation Error Message
Validation Error Message

Step 5 – Test the Cascading Drop-down Lists

Now that the data validation rules are set up, we can test the cascading drop-down list functionality. Selecting “Organization 1” causes the validation drop-down list in the Department column to reflect only those Departments associated with “Organization 1”. Selecting “Organization 2” causes the Department drop-down list to show those Departments associated with “Organization 2”.

Excel Cascading Drop-down List - Parent List Values
Parent List Values
Excel Cascading Drop-down List - Child List Values Based on Parent Selection
Child List Values Based on Parent Selection
Excel Cascading Drop-down List - Both Parent and Child Selected
Both Parent and Child Selected

Step 6 – Macro to Validate Parent / Dependent Relationship

Now that the worksheet is functioning as expected, you release it for users to update and someone will inevitably enter data in a way that breaks the parent / child relationship. As an example, “Organization 2” is selected in the Organization column and “Department 6” is selected in the Department column. The user then returns to the Organization column and changes the entry to “Organization 3”. The Department column retains the value of “Department 6” which is not valid for the “Organization 3” selection in the Organization column. To help avoid these errors, you can save the file as a macro enabled workbook and add the following code to the data entry worksheet. If this macro had been enabled, the “Department 6” value is removed as soon as the user selected “Organization 3”. This code assumes that the parent column is the first column (Column A) in the worksheet and the child column is immediately to the right (Column B). Please revise the code to meet your specific requirements.

Excel Cascading Drop-down List - Selecting Parent Field Clears Child Field Through Macro
Selecting Parent Field Clears Child Field Through Macro

Example

Excel Cascading Drop-down List – Example in Action
Excel Cascading Drop-down Lists in Action

Source Code

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    If Target.Column = 1 Then
        If Target.Validation.Type = xlValidateList Then
            If Not Target.Offset(0, 1).Validation.Value Then
                Target.Offset(0, 1).ClearContents
            End If
        End If
    End If
exitHandler:
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

Parse Cell Value in Excel Using Regular Expressions

Using Excel formulas to parse a substring from a cell value is usually difficult especially if the cell value or pattern is not easily defined. I have found that it requires various combinations of SEARCH, FIND, LEFT, RIGHT, MID, etc. The resulting formula is both unreadable and it does not successfully handle all cell values thus resulting in manual clean up. It would be nice if the SEARCH or FIND functions were able to use regular expressions by default.

The following VBA function allows you to parse a cell value based on a regular expression. To use this function, you must enable Microsoft VBScript Regular Expressions 5.5.

  1. On the Developer ribbon, click Visual Basic
  2. Once the Visual Basic window opens, go to Tools -> References…
  3. From the References dialog box, check/enable Microsoft Regular Expressions 5.5
  4. Click the OK button
Microsoft Excel Visual Basic Tools References - Microsoft VBScript Regular Expressions 5.5
Microsoft Excel Visual Basic Tools References – Microsoft VBScript Regular Expressions 5.5

Now that the regular expression reference is activated, insert a new module into the workbook if one doesn’t already exist. The code will not work if added directly to a worksheet object.

Add Module to Microsoft Excel VBA Project
Add Module to Microsoft Excel VBA Project

Add the below code to the module. You can then use it as part of formulas in the workbook, e.g. =RegExParse(A1,”ID[0-9]+”). This would parse the value in cell A1 returning the substring matching the pattern ID[0-9]+, e.g. ID0, ID1234, ID183274917234. I’ve also included an regular expression example where a slightly more complex pattern,e.g. US telephone numbers, is matched and extracted from a cell value.

VBA Function Results - Parse Value Using RegEx (Results)
VBA Function Results – Parse Value Using RegEx (Results)
VBA Function Results - Parse Value Using RegEx (Formula)
VBA Function Results – Parse Value Using RegEx (Formula)

Source Code

Option Explicit

Public Function RegExParse(val As String, SearchPattern As String) As String
  On Error GoTo errorHandler
  
  Dim regEx As New RegExp
  
  With regEx
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
    .Pattern = SearchPattern
  End With
  
  If regEx.Test(val) Then
    RegExParse = regEx.Execute(val)(0)
  Else
    RegExParse = CVErr(xlErrNA)
  End If
  
errorHandler:
  If Err.Number <> 0 Then
    RegExParse = CVErr(xlErrNA)
  End If
End Function

Microsoft InfoPath Lessons Learned

Microsoft InfoPath is a solid tool for creating customized forms to collect data, especially when integrated with SharePoint. Creating the form is simple through a WYSIWYG editor and a largely drag and drop process. The word “simple” applies only to creating the form. It typically becomes complicated as soon as the form is published (or attempted to be published). The form will start throwing weird errors with incomprehensible messages. If you do manage to publish the form, then it will exhibit strange behaviors or unexpected results.

Although Microsoft announced that InfoPath would be discontinued on January 31, 2014, I’m still using it to create new forms and thought I would share some lessons learned.

A duplicate field name “<field name>” was found

You have just created a fancy new InfoPath form and you attempt to publish it. Everything seems ready to go and then InfoPath displays the following error message:

A duplicate field name “<field name>” was found.

Others have posted workarounds that involve editing list definitions within the .CAB file. I wasn’t able to try these methods with my limited access to the server and effectively no administrative permissions.

In my case, the SharePoint site had a few global site columns defined which were used in a number of different lists. I was able to create and publish InfoPath forms for those other lists without an issue. However, this one particular list kept throwing the duplicate field name error.

The solution that worked in this instance was to create a new list column. I transferred the existing data from the site column to the newly created column. Once the data was copied, I deleted the site column from the list. I was able to leave the site column definition available in the site settings for the other lists that were working fine. I was then able to update the InfoPath form with the new column and publish without error. Unfortunately, the list used a number of site columns so I had to repeat the process for each site column in the list.

InfoPath Form Refreshes on Every Change in Field Focus

This behavior appears to happen in InfoPath forms with secondary data sources. Each time a form field loses focus, a postback is executed and the form refreshes. This creates a delay for the user and a noticeable screen flicker. For fields that do not need a postback, I have found that changing the “Browser forms” properties of the field to never send data will eliminate the problem. Using the REST web service API instead of connecting to an external SharePoint library or list as your secondary data connection seems to have a positive effect as well.

Microsoft InfoPath - Browser Forms Properties
Microsoft InfoPath – Browser Forms Properties

Long Running Script Warning in Internet Explorer

With InfoPath forms using a secondary data source to connect to an external SharePoint library or list, I have experienced errors in Internet Explorer when the user attempts to save the data. The browser will display the following warning message:

Stop running this script? A script on this page is causing your web browser to run slowly. If it continues to run, your computer might become unresponsive.

This happens consistently if the secondary data source is returning a large number of rows. As best as I can tell, the save action initiates a postback which triggers a refresh on the secondary data source. The time needed by Internet Explorer to parse and process the data will cause the “Stop running this script” warning message. This behavior appears to be limited only to certain versions of Internet Explorer. Firefox, Chrome, and other browsers behaved as expected and without incident.

As a workaround, a REST web service data source can be used instead of the connection to a SharePoint library or list. For a reason unknown to me, the postback and data source refresh for REST data sources does not occur when the form data is saved.

State Service Error Occurred While Processing Your Request

The InfoPath form has been published and users are now able to begin saving data. The form has been thoroughly tested and it is working perfectly…

…until the first non-test user attempts to save data and the following error is displayed:

Microsoft InfoPath - Critical Error State Service
Microsoft InfoPath – Critical Error State Service

This error message apparently means that the user does not have permission to enumerate files and folders in a Web site using SharePoint Designer and Web DAV interfaces. The user will either need to be assigned to a permission level that has this permission selected or it needs to be activated on one of the existing permission levels.

Microsoft InfoPath - Browse Directories Permission Setting
Microsoft InfoPath – Browse Directories Permission Setting

Using the SharePoint REST Web Service

SharePoint sites have a REST web service available through listdata.svc.It is accessible by calling the following URL (obviously adjust the parts in between <> for your particular site and remove the <> characters):

https://<site address>/_vti_bin/listdata.svc/<list name>

To return only a subset of columns in the list:

https://<site address>/_vti_bin/listdata.svc/<list name>?$select=<Column 1 Name>,<Column 2 Name>

To sort the data in a particular way:

https://<site address>/_vti_bin/listdata.svc/<list name>?$select=<Column 1 Name>,<Column 2 Name>&$orderby=<Column 1 Name>

To filter the data:

https://<site address>/_vti_bin/listdata.svc/<list name>?$select=<Column 1 Name>,<Column 2 Name>&$orderby=<Column 1 Name>&$filter=<Column 3 Name> eq 'XYZ' and substringof(<Column Name 5>, trim('<     ABC     >')) eq true

In this last example, I am filtering <Column 3 Name> to only return rows where it is equal to the string “XYZ” and where <Column Name 5> contains the substring “ABC” with leading and trailing whitespace removed. There are a number of functions and conditions available to filter data.

Creating a Rule to Change the REST URL in InfoPath

One of the actions available in InfoPath is “Change REST URL”. This is a nice feature because it allows for dynamic filtering of data returned from your external data source. Using the above example for filtering data using listdata.svc , you can create a formula in the action using concatenation and field data entered by the user in the form. As an example, a series of cascading select lists can be created using this method.

concat("https://<site address>/_vti_bin/listdata.svc/<list name>?$select=<Column 1 Name>,<Column 2 Name>&$orderby=<Column 1 Name>&$filter=<Column 3 Name> eq '", <Field Name 1>, "' and substringof(<Column Name 5>, trim('", <Field Name 7>, "')) eq true")

Add a second action to query using a data connection and the filtered results will be returned.

Detecting Changes on Multiple Selection Checkbox Fields

Rules and actions associated with changes to multiple selection checkbox fields do not behave as I would expect. A multiple selection checkbox field is a single field that contains multiple data values that can each be individually checked or unchecked. A rule set up with a condition to execute when the field changes will trigger as expected when items are checked. However, if a checked item is unchecked, the action will not be triggered.

In this example, assume the multiple selection checkbox field is called Products.

  • Create a text box field called “hidden_field”. This field does not need to be visible on the form.
  • On the field properties for hidden_field, set the “Default Value” to the Products field and check the “Refresh value when formula is recalculated” field.
  • On the field properties for Products, create a new rule that is triggered when the field is changed. Add an action to set hidden_field’s value equal to the Products field’s value.

Once these rules are set, checking or unchecking values in the Products field will result in the value of hidden_field changing. Since hidden_field is a standard text box and rules are triggered as expected on a text box, we can then create additional rules using hidden_field to execute based on changes.