Excel data validation drop down videos show how to automatically add new items to drop down lists with Excel macro, and more drop down list tips
Create an Excel Data Validation drop down list that allows users to add new items. New data validation items will be automatically added to the drop down list, and the list is sorted alphabetically.
For detailed instructions and an explanation of the Excel VBA code, see Excel Data Validation - Add New Items.
With this technique, you can select an item from an Excel drop down list, and see only the related items in the next drop down list. You'll limit the choices in an Excel data validation list, by using Excel named ranges and the INDIRECT function, to create dependent data validation lists.
In the example shown in this Excel video tutorial, if a country is selected from the first drop down, only that country's cities appear in the next column's drop down list.
Also, you can use the Excel IF function to make the selection more flexible. If no country is selected, show a list of world cities in the dependent drop down list.
With Excel data validation, you can select an item from an Excel drop down list, and see related items in the next drop down list. To do this, you set up Excel named ranges and use the INDIRECT function, to create dependent data validation lists.
In some cases though, uses will be able to enter invalid data in the dependent cell, if the first cell is empty. To prevent invalid entries, you can change the Ignore Blank setting. That can cause problems later, if you use the Circle Invalid Data command. Watch this video to see the details.
With this technique, you can select an item from an Excel drop down list, and then select another item, or several items, in the same drop down. You can store all the selected items in one cell, or fill them across the row, or down a column.
For written details, see the Contextures Blog post Select Multiple Items From Excel Data Validation List
Last updated: July 10, 2021 8:28 PM