This article demonstrates how to automatically create drop-down lists if adjacent data grows, there are two methods explained here. The first method uses an Excel defined Table.

The benefit of using a drop-down list in an Excel defined Table is that the whole table column contains drop-down lists automatically, it will also expand automatically if more values are added to the Table meaning you don't need to copy and paste drop-down lists.

You only need to copy a drop-down list and paste once to a cell in the Table column and Excel will instantly fill the remaining column cells with the same drop-down list.

The second method is an event procedure that makes sure there are drop-down lists in column A if there are adjacent values in column B and C.

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. Press with left mouse button on "Table" button
4. Select "My table has headers"
5. Press with left mouse button on OK!

Name Excel defined Table

1. Select any cell in the Table you just created.
2. Go to tab "Desing" on the ribbon.
3. Change the Table name to Table1.
4. Press Enter.

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

Apply data validations lists to the Table

1. Select cell A2
2. Go to tab "Data"
3. Press with left mouse button on "Data Validation" button
4. Allow:List
5. Source:INDIRECT("Table2[Region]")
6. Press with left mouse button on 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!

Get the Excel file

Apply-data-validation-lists-dynamically2.xlsm

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

Recommended articles

How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]

Back to top

Applying data validation lists dynamically (VBA)

The data validation lists contain values from a 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, instead of using an Excel defined Table I created a named range that expands. I have created comments to the VBA code I created, you can find it further down in this article.

Dynamic named range

1. Go to "Formulas" tab
2. Press with left mouse button on "Name Manager" button
3. Press with left mouse button on "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. Press with left mouse button on Close

Add VBA code to sheet

1. Press with right mouse button on on the sheet name.
2. Press with left mouse button on "View Code".
   
3. Paste VBA code to sheet module.
   

VBA code

'Event code that runs if a cell value changes.
'The Target argument contains the cell address that changed.
Private Sub Worksheet_Change(ByVal Target As Range)

'Dimension variables and declare data types
Dim Lrow As Single
Dim AStr As String
Dim Value As Variant

'Check if Target cell is located in column B or C or if a cell value changed in column E. 
If Not Intersect(Target, Range("$B:$C")) Is Nothing _
Or Not Intersect(Target, Range("E:E")) Is Nothing Then

    'Save last non-empty cell row in column B to variable Lrow
    Lrow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
    
    'Add values in named range Region to string variable AStr
    For Each Value In Range("Region")
        AStr = AStr & "," & Value
    Next Value

    'Populate a cell range in column A with drop-down lists, based on the number of rows in variable Lrow
    'The drop-down lists contain values from string variable AStr
    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

Get the Excel file

Apply-data-validation-lists-dynamically.xlsm

Back to top