You set up a data validation drop down list, so you can select valid items from a drop down list.
After you select a season, if you type an invalid month name in the adjacent cell, you'll see a warning message.
It's a great plan, but people are sometimes typing invalid entries in the month column, and the data validation isn't stopping them. What went wrong, and how can you fix it?
Problem With Blanks
In this example, the Month column has dependent data validation, based on the season that was selected in column B. If the Ignore Blank setting is turned on, then you'll be able to enter any value in a Month cell, if the Season cell is blank.
To prevent those invalid entries:
- Select the Data Validation cells
- On the Ribbon's Data tab, click Data Validation
- In the Data Validation dialog box, on the Settings tab, remove the check mark for Ignore Blank, then click OK
With the Ignore Blanks setting changed, if the Season cell is blank, you won't be able to enter anything in the Month column.
Blank Cells in Source List
You might see this problem in other situations too. For example, if the source list is a named range that contains blank cells, users may be able to type any entry, without receiving an error message.
To prevent this, turn off the Ignore Blank setting, as described above.
Error Alerts
If the data validation Error Alert is turned off, users will be able to type any entry, without receiving an error message. To turn the Error Alert on:
- Select the Data Validation cells
- On the Ribbon's Data tab, click Data Validation
- In the Data Validation dialog box, on the Error Alert tab, add a check mark for Show Error Alert After Invalid Data is Entered, then click OK
__________
