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

Related posts:

Dependent data validation lists in multiple rows

How to use a table name in data validation lists and conditional formatting formulas

Add values to a data validation list (vba)

Lookup between two lists of data to highlight missing data using conditional formatting in excel

Search for multiple text strings in multiple cells and use in data validation in excel