Article updated on January 09, 2018

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:

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