Using Validation (2003 sp2)

Topic

New Reply

If I am in a cell and I enter data, then use the mouse to click on another cell, the data entry is accepted.

I am validating a cell using a formula. When the users enter data into that cell, and then click on another cell, the validation error triggers the alert box. If the user hit the key, or key, all works well. For me, this is a non issue, but I had a user call today to tell me my spreadsheet was broken. I assume I am stuck with this behavior, but thought I would ask about it anyway. Fixing spreadsheets is easier than teaching a user how to enter data!

Reply | Quote

Replies

Sorry, I don’t understand.
If the user enters a value that passes the validation criteria, it should be accepted, whether the user clicks on another cell or presses Tab or Enter.
If the user enters a value that violates the criteria, an error message should appear, whether the user clicks on another cell or presses Tab or Enter.
What is the problem exactly?

Reply | Quote

Could you attach a scaled down workbook that demonstrates the problem, along with a description of exactly what needs to be entered into what cell and when to click on another cell to trigger the problem?

Reply | Quote

Here is the sample.

I find that if I change an existing value, I can click elsewhere with the mouse. If the cell is initially blank, I cannot click with the mouse.

I put the wrong instructions in the workbook. You can enter a number between 1 and 93. I believe you can also enter a value begining with an “R”.

Reply | Quote

There are three or four layers of indirectness in the validation, causing delays in the calculation. If you set the validation formula for cell B2 to

=OR(AND(B2>=1,B2=”R1″,B2=1,custZoneStart=”R1″, custZoneStart<="R93")).
The defined name custZoneStart refers to =Data_Entry!$B$2.
Excel has to evaluate all these defined names before validating)

Reply | Quote

I am confused why it would evalute fine when or is selected but not fine when using the mouse to navigate the speadsheet.

Reply | Quote

I’m not entirely sure either, but I think the crucial point is that the defined name custZoneStart contains the current value of cell B2, and it hasn’t been updated yet with the new value when the user clicks in another cell. By referring to B2 itself, validation does look at the new value.
Apparently the order of recalculating depends on how you navigate.

Reply | Quote