Inconsistant Validation (XL97)

Topic

New Reply

I am using data validation to allow only one answer per question in a range of five cells per row. (See attachment)
The Problem:
If you type a “X” in one of the cells and use the arrow or enter key, the validation works correctly, but if you type a “X” and use the mouse to click on the next row the validation allows more than one answer.
In other words the validation works different when using keyboard or mouse to accept input.

Reply | Quote

Replies

Just upon cursory glance, I’d question your strategy. Try this instead, I think it’s a lot easier.

Using cells M10:Q10 as an example, validate each cell in the group with =COUNTA($M$10:$Q$10)<2. You can do them all at once, of course. If the person adds an extra X using the mouse or the keyboard it stops them.

See your spreadsheet I'm sending back and look at those cells I reference above. I validated them for you (minus the alert text). It should make sense.

-Lenny

Reply | Quote

Thank You, the way you have done it works fine, but I would still like to know why the validation works inconsistantly the way I have done it in the first place. If I have done something wrong I would like to know what it is.

Frits

Reply | Quote

It doesn’t work because the recalculation on the cells your validation is dependent on doesn’t take place until after the validation is done.

Reply | Quote

Legare
I don’t agree 100% with you. The validation works if I use the enter key after typing in one of the cells. It does not work correctly if I use the mouse to click somewhere else on the sheet after typing in a cell.

The following quote comes out of the Excel Help under the heading “Determine valid entries for a cell by using a formula”:
“Before evaluating the formula, Microsoft Excel recalculates the worksheet with the data entered in the cell.”

This does not correspond with your statement?

Reply | Quote

It does the same on my system. However, on my system, I see the border appear around the cell which I click on before I see the cell with the formula recalculate which indicates to me that the selection change and validation (since the selection would not change if validation failed) were done before the recalculate. That is, IMHO, a bug in Excel code.

Reply | Quote

Thank You for the response.

Does anyone know if this is a recorded bug and is it also a problem in Office XP?

Reply | Quote

Just to confuse things a little further, in your example, I can click on cell n7, press enter to drop to cell n8, press enter again and not get an error message. Also if I click on cell n8 and add an additional “x” so it reads “xx” and press enter it will give the error message, then if you click cancel then enter you can drop to cell n9 even though it is still an error.
These little boxes we play with every day are just filled with magic, aren’t they!!
Stats

Reply | Quote

Select M7:M12.
Activate Data|Validation.
Choose Custom for Allow.
In the formula box enter:

=COUNTIF($M$7:$M$12,”X”)<2

Click OK.

Apply the above to other columns.

The above makes AF7:AH12 superfluous.

Note that data validation can be rendered ineffective by copying & pasting.

Aladin

Reply | Quote