Data Validation copy and paste bug

[Ryan W.]

I'm not sure if this is a bug or the incorrect use of a custom data
validation formula. I'm trying to check to see if more than one value
of "X" has been placed into a range of cells. Here is the custom data
validation formula I am using:

=IF(COUNTIF(B55,"X") > 1,0,1)

Everything seems to work fine until I attempt to copy and paste a
character into one of the cells in the range...validation does not
trigger. The other instance when validation doesn't trigger is when I
start adding the "X" to the last cell in the range and go backwards.

Any suggestions on how to fix this?

Thanks,

Ryan

 

[Peo Sjoblom]

For the former that is default, validation does not work
if you paste values into the validated area

For the latter use this instead



=IF(COUNTIF($B$5:$D$5,"X") > 1,0,1)

Of course the IF part is obsolete

=COUNTIF($B$5:$D$5,"X") <=1

will do the same

--


Regards,


Peo Sjoblom