Data Validation‎ > ‎

Self Expanding DV

"I use data validation drop down lists, but I would like to be able to type new options into these cells and have what I typed become a permanent option in the drop down list itself so all cells using that DV list are updated with this new option in real time."
  1. Technique uses named ranges for the Data Validation List source
  2. If a cell with a DV list is used to allow a new entry not in the DV list, the item is added to the drop list, too
  3. The Drop Down lists self-expand
PART 1 - Dynamic Named Ranges
Instead of hardcoding the range of cells that make a list of options for a data validation source, we use a formula instead.  The OFFSET() function allows functions to be used that will result in a "range" being defined by however many items are in a particular column.
In the example sheet attached above, we see the named range Users is defined with the formula:
This formula anchors the range at cell A2, the uses the COUNTA() function to count the number of used cells in column A and uses that for the ROWS parameter, automatically causing the range to expand simply by tpying more adjacent entries into column A.  We subtract 1 from the COUNTA because column A has a title cell in A1 that we are not using.


There are additional dynamic named ranges for Animals and Cars in the sample file that do the same thing in columns B and

PART 2 - The Form Cells
Typical settings are used for a Data Validation "list" using the named range as the source.
The key is to remember to select the Error Alert tab and uncheck the "alert" option, this allows free typing in the cell(s), too.
PART 3 - VBA code to add to the named ranges when new items are typed in
The following code is installed into the Form sheet module.  Right-click that sheet tab and select View Code to see the code module in the example sheet.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'Author:    Jerry Beaucaire,  10/1/2012
'Summary:   New entries made in cell with DV drop downs are permanently added to the DV lists
Dim strValidationList As String
Dim strVal As String
Dim lngNum As Long

strValidationList = Mid(Target(1).Validation.Formula1, 2)
strVal = Target(1).Value
On Error Resume Next
With Sheets("Lists")
    Select Case strValidationList
        Case "Users"
            lngNum = WorksheetFunction.CountIf(.Range("A:A"), strVal)
            If lngNum = 0 Then
                .Range("A" & Rows.Count).End(xlUp).Offset(1).Value = strVal
                .Range("A:A").Sort .Range("A2"), xlDescending, Header:=xlYes
            End If
        Case "Animals"
            lngNum = WorksheetFunction.CountIf(.Range("B:B"), strVal)
            If lngNum = 0 Then
                .Range("B" & Rows.Count).End(xlUp).Offset(1).Value = strVal
                .Range("B:B").Sort .Range("B2"), xlDescending, Header:=xlYes
            End If
        Case "Cars"
            lngNum = WorksheetFunction.CountIf(.Range("C:C"), strVal)
            If lngNum = 0 Then
                .Range("C" & Rows.Count).End(xlUp).Offset(1).Value = strVal
                .Range("C:C").Sort .Range("C2"), xlAscending, Header:=xlYes
            End If
    End Select
End With
End Sub


Nothing says "thanks" like a steak dinner!

PayPal - The safer, easier way to pay online!