Data Validation Error Does Not Work?

Operator error I am sure,but I am unable to get data validation to function
correctly. It is a simple list (yes,no from a 2 cell named range), ignore
blank is not checked. I want an error if the user does not make a selection.
The show error box is checked. BUT, if the field is left blank, the message
does not show up. (Just as a check I type something else in the field and
the message does show up). What have I done wrong? Thank you.

No matter what data validation settings you've applied, users will be
able to leave the cell blank, or select the cell, and press the Delete
key, to clear the cell.

However, if the cursor is in the cell, or in the formula bar, they won't
be able to press the Delete key, to clear the cell, or press the Enter
key while the cell is blank.

You could use programming to check for blank cells, or make other cells
show an error if the cell is blank.

Alex Mackenzie wrote:
> Operator error I am sure,but I am unable to get data validation to function
> correctly. It is a simple list (yes,no from a 2 cell named range), ignore
> blank is not checked. I want an error if the user does not make a selection.
> The show error box is checked. BUT, if the field is left blank, the message
> does not show up. (Just as a check I type something else in the field and
> the message does show up). What have I done wrong? Thank you.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Hi ppls,

Can I just confirm that this is a known bug by Microsoft? I have
encountered the same thing in Excel 2002:

I have a named range which I use for a drop down list box with validation
*supposed* to only allow values which are in that list. However, if I enter
the cell and type something else, it is perfectly happy to let me do it.

If I change the source from the named range to manually entering the range
in the edit box, the drop down list and data validation work perfectly and if
I enter the cell and type something not in the list, I get my error message.

I'm sure I could make up a macro or something to manually validate the
contents of the cell, but then there wouldn't really be much point in having
a data validation function in Excel then would there

Regards

Q-ee

"Debra Dalgleish" wrote:

> No matter what data validation settings you've applied, users will be
> able to leave the cell blank, or select the cell, and press the Delete
> key, to clear the cell.
>
> However, if the cursor is in the cell, or in the formula bar, they won't
> be able to press the Delete key, to clear the cell, or press the Enter
> key while the cell is blank.
>
> You could use programming to check for blank cells, or make other cells
> show an error if the cell is blank.
>
> Alex Mackenzie wrote:
> > Operator error I am sure,but I am unable to get data validation to function
> > correctly. It is a simple list (yes,no from a 2 cell named range), ignore
> > blank is not checked. I want an error if the user does not make a selection.
> > The show error box is checked. BUT, if the field is left blank, the message
> > does not show up. (Just as a check I type something else in the field and
> > the message does show up). What have I done wrong? Thank you.
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

It's probably "planned behaviour."

If the named range contains blank cells, users can type any value in the
cell with the data validation list.

If the data validation list is based on a range address, users can only
enter values from the list, even if the source range contains blank cells.

Q-ee wrote:
> Hi ppls,
>
> Can I just confirm that this is a known bug by Microsoft? I have
> encountered the same thing in Excel 2002:
>
> I have a named range which I use for a drop down list box with validation
> *supposed* to only allow values which are in that list. However, if I enter
> the cell and type something else, it is perfectly happy to let me do it.
>
> If I change the source from the named range to manually entering the range
> in the edit box, the drop down list and data validation work perfectly and if
> I enter the cell and type something not in the list, I get my error message.
>
> I'm sure I could make up a macro or something to manually validate the
> contents of the cell, but then there wouldn't really be much point in having
> a data validation function in Excel then would there
>
> Regards
>
> Q-ee
>
> "Debra Dalgleish" wrote:
>
>
>>No matter what data validation settings you've applied, users will be
>>able to leave the cell blank, or select the cell, and press the Delete
>>key, to clear the cell.
>>
>>However, if the cursor is in the cell, or in the formula bar, they won't
>>be able to press the Delete key, to clear the cell, or press the Enter
>>key while the cell is blank.
>>
>>You could use programming to check for blank cells, or make other cells
>>show an error if the cell is blank.
>>
>>Alex Mackenzie wrote:
>>
>>>Operator error I am sure,but I am unable to get data validation to function
>>>correctly. It is a simple list (yes,no from a 2 cell named range), ignore
>>>blank is not checked. I want an error if the user does not make a selection.
>>> The show error box is checked. BUT, if the field is left blank, the message
>>>does not show up. (Just as a check I type something else in the field and
>>>the message does show up). What have I done wrong? Thank you.
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html