Table of Contents

  1. Applying Drop Down lists dynamically using an Excel Defined table
  2. Applying Drop Down lists dynamically (vba)

Add Drop Down lists automatically

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!

Name above table: Table1

Repeat the steps above with cell range E1:E8, name the Excel defined Table: Table2

Learn more about Excel Defined Tables:

Become more productive – Learn Excel Defined Tables

An Excel table allows you to easily sort, filter and sum values in a data set where values are related.

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:INDIRECT("Table2[Region]")
  6. Click Ok!

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

This article demonstrates how to use excel defined tables in a drop down list:

Learn this genius trick on how to use a Table name in Data Validation Lists and Conditional Formatting formulas

David Hager gave this valuable comment about how to reference a table name in conditional formatting formulas: =INDIRECT("Table1[Start]") Watch this video to […]

Download excel *.xlsm file


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
        .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