Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Data Validation does not work when selecting another cell.
I have a cell with data validation rules set. When an invalid entry is typed
in and the Enter key pressed, the rules work fine and reject the entry. However, if an invalid entry is typed in and the another cell is selected with the mouse (without pressing Enter), the invalid data is accepted in the cell. Is there any way of stopping this ? Thanks Simon |
#2
|
|||
|
|||
It worked for me when I tried it.
You may want to post more details or try it again. Simon Jefford wrote: I have a cell with data validation rules set. When an invalid entry is typed in and the Enter key pressed, the rules work fine and reject the entry. However, if an invalid entry is typed in and the another cell is selected with the mouse (without pressing Enter), the invalid data is accepted in the cell. Is there any way of stopping this ? Thanks Simon -- Dave Peterson |
#3
|
|||
|
|||
I've never seen that behaviour. What validation is set on the cell? What
version of Excel are you using. Simon Jefford wrote: I have a cell with data validation rules set. When an invalid entry is typed in and the Enter key pressed, the rules work fine and reject the entry. However, if an invalid entry is typed in and the another cell is selected with the mouse (without pressing Enter), the invalid data is accepted in the cell. Is there any way of stopping this ? Thanks Simon -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
|
|||
|
|||
Debra
Thanks for the reply. I have recreated the error in the following example. For instance, in a blank worksheet, put the formula "+b1" in cell a1. In cell B1, the validation should be Custom and the formula being =A1=0. This implies that the entry into cell B1 should not cause cell A1 to go nagative. If you enter -1 in cell B1, it correctly produces a validation error. However, if you sellect cell B1 and type -1 without pressing enter but instead just select another cell on the worksheet, the entry is accepted. Note, to cause this strange error, cell B1 has to be empty before typing -1 and selecting another cell. I am using Excel 2002. Thanks for your help. Simon "Debra Dalgleish" wrote: I've never seen that behaviour. What validation is set on the cell? What version of Excel are you using. Simon Jefford wrote: I have a cell with data validation rules set. When an invalid entry is typed in and the Enter key pressed, the rules work fine and reject the entry. However, if an invalid entry is typed in and the another cell is selected with the mouse (without pressing Enter), the invalid data is accepted in the cell. Is there any way of stopping this ? Thanks Simon -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#5
|
|||
|
|||
Dave
Thanks for the reply. I have recreated the error in the following example. For instance, in a blank worksheet, put the formula "+b1" in cell a1. In cell B1, the validation should be Custom and the formula being =A1=0. This implies that the entry into cell B1 should not cause cell A1 to go nagative. If you enter -1 in cell B1, it correctly produces a validation error. However, if you sellect cell B1 and type -1 without pressing enter but instead just select another cell on the worksheet, the entry is accepted. Note, to cause this strange error, cell B1 has to be empty before typing -1 and selecting another cell. I am using Excel 2002. Thanks for your help. Simon "Dave Peterson" wrote: It worked for me when I tried it. You may want to post more details or try it again. Simon Jefford wrote: I have a cell with data validation rules set. When an invalid entry is typed in and the Enter key pressed, the rules work fine and reject the entry. However, if an invalid entry is typed in and the another cell is selected with the mouse (without pressing Enter), the invalid data is accepted in the cell. Is there any way of stopping this ? Thanks Simon -- Dave Peterson |
#6
|
|||
|
|||
Well, I can recreate that, but don't know why it happens. Perhaps the
cross referencing is confusing Excel. You could change the data validation in B1 to Decimal, where B1 is = 0, and that should prevent the problem from occurring. Simon Jefford wrote: Debra Thanks for the reply. I have recreated the error in the following example. For instance, in a blank worksheet, put the formula "+b1" in cell a1. In cell B1, the validation should be Custom and the formula being =A1=0. This implies that the entry into cell B1 should not cause cell A1 to go nagative. If you enter -1 in cell B1, it correctly produces a validation error. However, if you sellect cell B1 and type -1 without pressing enter but instead just select another cell on the worksheet, the entry is accepted. Note, to cause this strange error, cell B1 has to be empty before typing -1 and selecting another cell. I am using Excel 2002. Thanks for your help. Simon "Debra Dalgleish" wrote: I've never seen that behaviour. What validation is set on the cell? What version of Excel are you using. Simon Jefford wrote: I have a cell with data validation rules set. When an invalid entry is typed in and the Enter key pressed, the rules work fine and reject the entry. However, if an invalid entry is typed in and the another cell is selected with the mouse (without pressing Enter), the invalid data is accepted in the cell. Is there any way of stopping this ? Thanks Simon -- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data validation | Excel Worksheet Functions | |||
data validation list drop down text format too small | Excel Worksheet Functions | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Automatic Data Validation drop down creation | Excel Discussion (Misc queries) | |||
Data Validation | Excel Discussion (Misc queries) |