Search Contextures Sites
Excel Video Tutorials -- Data Validation
How to add new items in a drop down list, show different drop downs in the same cell, and more video 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
Contextures Inc., Copyright ©2016
All rights reserved.
Last updated: May 31, 2016 7:27 PM