Forum Discussion
Cruxy123
Dec 20, 2023Copper Contributor
Adding data to a drop down list
I have attempted to add data to a previously set up drop down list associated with a cell, I am unable to add another item?
Rodrigo_
Dec 21, 2023Steel Contributor
Cruxy123
When adding a data for you drop down list, you also need to update your SOURCE on Data Validation Criteria. See the image below
If you do not want to edit your Data Validation Source, I have 2 suggestions method for ya'.
- Using OFFSET and COUNTA Functions
- Select the cell where you want to put the drop-down list.
- Go to the Data tab >> Data Validation group >> Data Validation.
- In the Data Validation dialog box, select List from different options under Allow.
- Type the following formula in the Source box: =OFFSET($A$2,0,0,COUNTA(A:A)-1).
- Here, $A$2 is the starting cell of the range, the following 2 zeros are indicating that the cell reference will not move by any row or column number. Finally, COUNTA(A:A)-1 is indicating the height number of the range which will be the number of rows having texts or numbers.
- Defining Name for a Range and Creating Table
- Select the source range.
- Click the tab “Formula” in the ribbon.
- Click the button “Define Name” in the toolbar.
- Input a name into the “Name” Text Box.
- Input the range into the “Refers to” text box.
- Click a cell within the source range.
- Click the tab “Insert” in the ribbon.
- Click the button “Table” in the toolbar
Here's the source/reference: 2 Methods to Auto Refresh the Drop-Down List in Your Excel Worksheet