Excel – The BEST Dynamic Data Validation Dropdown List

Excel Data Validation lists are a great way to

  • expedite data entry
  • ensure consistent data entry
  • reject undesirable information
SNAGHTML12ae70b

One of the few and far between shortcoming of Data Validation lists is the fact that with long lists, they have to exist somewhere in the workbook.  The existing list supplies the entries displayed when the dropdown list is activated.

SNAGHTML12e6024

The existence of the list is not in and of itself an issue, maintaining the list (adding and removing items from the list) and having those changes propagate to the Data Validation dropdown is the problem.  Over the years there have been some very creative solutions developed by some truly ingenious people.  The process below appears to be the most straight-forward and effective solution developed.

Step 1:  Create a list

If you are providing lists for several Data Validation dropdown lists, a best practice is to create a separate sheet (called “DDLists”, perhaps) and localize all of the lists to this sheet, one list per column.

SNAGHTML135e3d9

Step 2:  Convert each list into an official Excel Data Table

Select a list (perform this step separately for each list) and navigate to the Home ribbon.  In the Styles group select Format as Table.  Select the color scheme of your choice.

SNAGHTML138db3f

Repeat this process for each list on the sheet.

SNAGHTML167779e

Step 3:  Name each data table

Selecting each table (one at a time), select the Table Tools – Design ribbon.  In the Properties group (far left side of ribbon), change the table name to something memorable; i.e. “tblRegions”.

SNAGHTML16e2035

If the tables are prefaced with something like “tbl”, “t”, or “tb”, all of the tables will appear grouped together in the Name Manager and when using Excel AutoComplete feature during hand-typed formulas.

imageSNAGHTML1431171

Step 4:  Create the Data Validation rule

Select the cell where the dropdown list is to appear.  From the Data ribbon, in the Data Tools group, select Data Validation.

image

In the Data Validation dialog box, from the Allow dropdown, select List.

image

In the Source field, type the following formula:

    =INDIRECT(“tblRegions[Regions]”)   

image

The parts of the formula are as follows:

=INDIRECT(“tblRegions[Regions]“)

INDIRECT è The function used to retrieve the list

tblRegions è The name of the Data Table

[Regions] è The name of the column holding the list

**** IMPORTANT ****

Ensure you enclose the table and column name in double quotes

A dropdown arrow will appear allowing for list entries to be selected.

SNAGHTML15263ee

The Best Part

If an new entry has to be included in a list, simply add the entry to the bottom of the list.  The data table will automatically expand to absorb the new item.

SNAGHTML154c82cSNAGHTML155e0d0

If you wish the list to be sorted, click on any item in the list.  From the Data ribbon, in the Sort & Filter group, select Ascending Sort.

SNAGHTML1586c4d

The result will be a sorted list.

SNAGHTML159571aSNAGHTML159f176

BONUS TIP

If you have several single column lists (like the ones above), and you wish to keep all of those lists sorted, the VBA code below will sort the lists after all of the modifications are made to the lists.  Make your changes to the list(s); upon leaving the sheet, the VBA code will run and loop through all of the tables on that sheet, sorting each list alphabetically in ascending order.

Copy and paste the below code to the code sheet of the sheet holding all of the lists.

VBA CODE

Private Sub Worksheet_Deactivate()                        

Dim FirstCell As String                                  
Dim tbl As ListObject                                     

For Each tbl In ActiveSheet.ListObjects                  
                                                         
        tbl.DataBodyRange.Cells(1, 1).Select             
                                                         
        FirstCell = ActiveCell.Address                   
                                                         
            With tbl.Sort                                
                .SortFields.Clear                        
            End With                                     
                                                         
            With tbl.Sort                                
                .SortFields.Add Key:=Range(FirstCell), _ 
                 SortOn:=xlSortOnValues, _               
                 Order:=xlAscending, _                   
                 DataOption:=xlSortNormal                
            End With                                     
                                                         
            With tbl.Sort                                
                .Header = xlYes                          
                .MatchCase = False                       
                .Orientation = xlTopToBottom             
                .SortMethod = xlPinYin                   
                .Apply                                   
            End With                                     
                                                         
    Next tbl                                              

End Sub