How do you add rows to a drop down list automatically Dynamic Data Validation lists?
How to automatically add new items to a drop down listAuthor: Oscar Cronquist Article last updated on April 21, 2021 Show
A drop-down list in Excel prevents a user from entering an invalid value in a cell. Entering a value that is not in the drop-down list will not cause an error alert. My worksheet allows you to type a new value and it is instantly included to the drop-down list. The drop-down list contains unique distinct values extracted from cells in the excel defined table. The following animated picture shows what I am trying to explain. The great thing about drop-down lists and excel defined tables is that you don't need to copy the drop-down list to new cells in the table. The Excel-defined table does that for you. What's on this webpage
There is no VBA in this post. How is this done? 1. Insert an Excel defined tableInsert an Excel defined table or convert an existing range. You can use the shortcut keys CTRL + T or
The table is now formatted differently,this makes it easy to identify Excel Tables on a worksheet. Back to top 2. Insert a new "helper" sheetInsert a new sheet, it is going to be our "helper" sheet. I renamed it "Sheet2". The formula in cell A2 extracts unique distinct values from Table1[Name]. Enter this array formula in cell A2: =IFERROR(INDEX(Table1[Name]&"", MATCH(0,COUNTIF('Sheet2'!$A$1:A1, Table1[Name]), 0)), "") Make sure you enter it as an array formula. If you don't know how follow these steps:
If you did this right the formula now begins with a curly bracket { and ends with a curly bracket }. Don't enter these characters yourself. Copy cell A2 and paste it to cells belowas far as needed. Update! Excel 365 dynamic array formula: =UNIQUE(Table1[Name]) A dynamic array formula is not entered as an array formula. It works like a regular formula, press Enter only. Read more about the UNIQUE function. 2.1 Explaining array formula in cell A2Step 1 - Count cells based on a conditionThe COUNTIF function counts cells that meet a condition. COUNTIF('Sheet2'!$A$1:A1, Table1[Name]) becomes COUNTIF("Unique values",{"Elsa";"Ellen";"Ella";"Ellie";"Anna";"Ellen"}) and returns {0; 0; 0; 0; 0; 0}. Step 2 - Find relative position of the frist 0 (zero) in the arrayThe MATCH function returns the position of a given value in a cell range or array. MATCH(lookup_value, lookup_array, [match_type]) MATCH(0,COUNTIF('Sheet2'!$A$1:A1, Table1[Name]), 0) becomes MATCH(0,{0; 0; 0; 0; 0; 0}, 0) and returns 1. Step 3 - Get value based on the relative positionThe INDEX function returns a value from a cell range or array based on a row and column number. INDEX(array, [row_num], [column_num]) INDEX(Table1[Name]&"", MATCH(0,COUNTIF('Sheet2'!$A$1:A1, Table1[Name]), 0)) becomes INDEX(Table1[Name]&"", 1) becomes INDEX({"Elsa"; "Ellen"; "Ella"; "Ellie"; "Anna"; "Ellen"}&"", 1) and returns "Elsa" in cell A2. Step 4 - Return a blank if no value is found and an error is returnedIFERROR(INDEX(Table1[Name]&"", MATCH(0,COUNTIF('Sheet2'!$A$1:A1, Table1[Name]), 0)), "") becomes IFERROR("Elsa", "") and returns "Elsa". Back to top 3. Create a new Excel Named Range
Back to top 4. Build a Drop-Down List
Back to top 5. Disable data validation error alertThe "Error alert" ta in the data validation window allows you to remove the error alert that is shown after an invalid value is entered.
Disabling the error alert allows us to use the drop-down list if we want or type a new value in the cell. Back to top 6. A sorted drop-down list from A to ZThe drop-down list in this sheet contains sorted text values, get the file below if you want to know how it is made. Back to top If you want to learn more about array formulas join Advanced excel course. Get the Excel fileDynamic-drop-down-list-add-new-values-automaticallyv2.xlsx Back to top Weekly Blog EMAIL Email Welcome! I am Oscar and here to help you out. Feel free to comment and ask Excel questions. Make sure you subscribe to my newsletter so you don't miss new blog articles. Related articlesCreate dependent drop down lists containing unique distinct values This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We [] Populate drop down list with unique distinct values sorted from A to Z Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using [] Apply dependent combo box selections to a filter Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. [] 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 [] Dependent drop-down lists in multiple rows This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on [] Change chart data range using a Drop Down List [VBA] In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above [] Run a Macro from a Drop Down list [VBA] This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two [] Change PivotTable data source using a drop-down list In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down [] Create a drop down list containing alphabetically sorted values This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list [] Comments (15) 15 Responses to How to automatically add new items to a drop down list
Leave a ReplyClick here to cancel reply. Name (required) Mail (will not be published) (required) Website
Δ How to commentHow to add a formula to your comment How to add VBA code to your comment How to add a picture to your comment: Contact Oscar You can contact me through this contact form |