Data validation is not displaying error alert for blank cells even when "Ignore blank" is not checked
I have a table including columns for the numbers of year, month, and day. The month and day don't have to be specified, but if they're not I want the values to be 0 rather than left blank. I've set up data validation to limit the allowable values to lists (one for month, one for day) that don't contain any blanks. "Ignore blank" is not checked, and I've set up an error alert to be displayed when invalid data is entered. It works correctly if I enter any value that's not in the table. But if I leave the cell blank, the alert isn't displayed. See screenshot of relevant cells and validation settings below.
Here are things I've checked and alternatives I've tried:
- The fields are defined as numbers.
- I used ISBLANK in another cell to confirm that the cells really are blank.
- It happens whether I delete the value in an existing row or enter a new row and leave the cell blank.
- Entering the allowable values as whole numbers between 0 and 12 doesn't display the alert.
- Entering the allowable values as a list with the source as the range in the Month values column (which has no blanks) doesn't display the alert.
- Entering the allowable values as a list with the values directly in the Source field (which has no blanks at the beginning, the end, or between items) doesn't display the alert, but I do get an error indicator in the cell, saying "The value in this cell is invalid or missing,"
- Presence or absence of an in-cell dropdown doesn't affect the behavior.
- The file is one I've had for several years and tinkered with a lot, only recenlty converted it to a table, and only after that added the validation. In case there was something left somewhere after all those changes that was causing the behavior, I've created the smaller sample version in the screenshot, entering all the data, formulas, and validation from scratch. The problem remains.
Other potentially useful information:
- I'm using Excel 365 desktop app.
- The date elements are in separate columns because many of the dates are before 1900 and Excel can't handle them correctly. The years alone are sufficient for my calculations at this point, but it's helpful to have the have the month and day when they're known.
I've used data validation to produce error alerts for blank cells many times before and this is the first time this has happened. Any ideas? Anything I've overlooked?
[link] [comments]
Want to read more?
Check out the full article on the original site