In this video I explain how to include new items in Excel drop-down lists automatically. These are referred to as Dynamic Data Validation Lists because they automatically extend to include new rows/cells added to the source data range. Download the Excel file: https://www.excelcampus.com/tips/dynamic-data-validation/
In this example we will use Excel Tables and named ranges to create the dynamic lists. The named range references a single Table column using structured references (Table Name[Column Name]). The named range includes all cells in the column and never needs to be updated or maintained.
This means we don’t have to remember to update the source data range every time we make changes to it. It saves us a lot of time and helps prevent errors.
Checkout by video on an Introduction to Drop-down Lists to learn more about creating data validation lists and all the rules that go along with them. https://youtu.be/KGnvCKiOLM0
Compatibility: This solution uses Excel Tables that were introduced in Excel 2007 for Windows. It will work in all versions of Excel including Excel 2007, 2010, 2016, 2019, Office 365 for Windows and Excel 2011, 2016, and beyond for Mac.
The Free List Search Add-in:
Unfortunately we can’t directly search the drop down lists within Excel. So I created a free add-in called List Search that allows you to search any drop down list and input the value in the cell.
It’s packed with additional time saving features. Click the link below to learn more and download List Search.
Download The List Search Add-in: https://www.excelcampus.com/vba/search-data-validation-drop-down-lists/
Other Videos and Resources:
– How to create drop-down lists: https://youtu.be/KGnvCKiOLM0
– Beginners Guide to Excel Tables: https://youtu.be/fJnJTff4Na8
– How to search drop-down lists: https://youtu.be/kntHn2J7pdk
– Update to List Search Add-in: https://youtu.be/igPO79uYoGg
– Dependent Cascading Drop Down Lists: https://www.excelcampus.com/tables/dependent-drop-lists/