Apply drop-down lists dynamically
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
- Applying Drop Down lists dynamically using an Excel Defined table
- Applying Drop Down lists dynamically (VBA)
Add Drop Down lists automatically
Create a table
- Select cell range A1:C11
- Go to tab "Insert"
- Press with left mouse button on "Table" button
- Select "My table has headers"
- Press with left mouse button on OK!
Name Excel defined Table
- Select any cell in the Table you just created.
- Go to tab "Desing" on the ribbon.
- Change the Table name to Table1.
- Press Enter.
Repeat the steps above with cell range E1:E8, name the Excel defined Table: Table2
Apply data validations lists to the Table
- Select cell A2
- Go to tab "Data"
- Press with left mouse button on "Data Validation" button
- Allow:List
- Source:INDIRECT("Table2[Region]")
- 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!
This article demonstrates how to use excel defined tables in a drop-down list:
Recommended articles
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]
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
- Go to "Formulas" tab
- Press with left mouse button on "Name Manager" button
- Press with left mouse button on "New.."
- Type Region
- Type in source field:
=OFFSET('Data Validation Lists'!$E$2, 0, 0, COUNTA('Data Validation Lists'!$E:$E)-1)
- Press with left mouse button on Close
Add VBA code to sheet
- Press with right mouse button on on the sheet name.
- Press with left mouse button on "View Code".
- 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
Data validation category
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]
This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]
Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]
A drop-down list in Excel prevents a user from entering an invalid value in a cell. Entering a value that […]
This article demonstrates a basic invoice template I created. It lets you use dropdown lists to quickly select products on […]
I will in this article demonstrate how to use a value from a drop-down list and use it to do […]
I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the […]
This article describes how to create a map in Excel, the map is an x y scatter chart with an […]
Josh asks: I have this working right now with 6 drop downs/lists. I wanted to see if you possibly know […]
Aynsley Wall asks: I have a spreadsheet that I use for 3 different companies. What I would really like to […]
This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]
The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]
Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that […]
Macro category
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
In this blog article, I will demonstrate basic file copying techniques using VBA (Visual Basic for Applications). I will also […]
Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]
This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
Today I would like to share with you these small event handler procedures that make it easier for you to […]
This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from […]
This article demonstrates macros that save worksheets to a single pdf file. What's on this webpage Export all worksheets in […]
Functions in this article
More than 1300 Excel formulas
Excel categories
5 Responses to “Apply drop-down lists dynamically”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
I read through your blog and I do have question.
Why not just use the built-in table functionality and have Excel do the VBA portion automatically?
I want to use the Excel sheet for Dynamic purpose.
Various product numbers that come from scanning barcodes need to be entered in column A. I want to ensure that if the product number entered (scanned) starts with "200", the cell gets locked automatically after the product number is entered. If any other product number is entered, the cell doesn't get locked.
I also want to change the cell colour to red for product code series starting other than "200".
Good Day Sir
i wan to ask help with my excel file for example theres a province with dedicated barangay pls help me if i choose that province in a validation list it will appear its assigned brgy
Hi Ocscar,
I am using this code with modifications but doesn't seem to be working dynamically. Could you please assist?
Below is the code:
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("$C:$C")) Is Nothing _
Or Not Intersect(Target, Range("C:C")) Is Nothing Then
Lrow = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row
For Each Value In Range("Course_ID_DigitalVBA")
AStr = AStr & "," & Value
Next Value
With ActiveSheet.Range("A10: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