Table of Contents

  1. Applying data validation lists dynamically using a table
  2. Applying data validation lists dynamically (vba)

Applying data validation lists dynamically using a table

Create a table

  1. Select cell range A1:C11
  2. Go to tab "Insert"
  3. Click "Table" button
  4. Select "My table has headers"
  5. Click OK!

Create a named range

    1. Go to tab "Formulas"
    2. Click "Name manager" button
    3. Click "New..."
    4. Name: Regiontable
    5. Refers to:

=OFFSET('Data Validation Lists (table)'!$E$2, 0, 0, COUNTA('Data Validation Lists (table)'!$E:$E)-1)

  1. Click OK

Apply data validations lists to table

  1. Select cell A2
  2. Go to tab "Data"
  3. Click "Data Validation" button
  4. Allow:List
  5. Source:=Regiontable
  6. Click Ok!

The entire first column is now containing a drop down list in each cell. If the table expands, the new cell has a drop down list!

Download excel *.xlsm file

Apply-data-validation-lists-dynamically2.xlsm

Applying data validation lists dynamically (vba)

The data validation lists contain values from named range. The named range (E2:E8) expands when you add new values to the list. The animated gif shows you that.

The animated gif below also shows you when adding a new company name in cell B11, a drop down list (Data validation list) is instantly applied to cell range A2:A11. The vba code in this sheet and a named formula make it all happen!

Let me explain how I created this sheet

Dynamic named range

  1. Go to "Formulas" tab
  2. Click "Name Manager" button
  3. Click "New.."
  4. Type Region
  5. Type in source field:
    =OFFSET('Data Validation Lists'!$E$2, 0, 0, COUNTA('Data Validation Lists'!$E:$E)-1)

  6. Click Close

Add vba code to sheet

  1. Right click on sheet name
  2. Click "View Code"
  3. Paste vba code

VBA code

Private Sub Worksheet_Change(ByVal Target As Range)
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Lrow As Single
Dim AStr As String
Dim Value As Variant
If Not Intersect(Target, Range("$B:$C")) Is Nothing _
Or Not Intersect(Target, Range("E:E")) Is Nothing Then
    Lrow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
    For Each Value In Range("Region")
        AStr = AStr & "," & Value
    Next Value
    With ActiveSheet.Range("A2:A" & Lrow).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=AStr
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End If
End Sub

Download excel *.xlsm file

Apply data validation lists dynamically.xlsm