Data Validation copy and paste bug

  • Thread starter Thread starter Ryan W.
  • Start date Start date
R

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(B5:D5,"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
 
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
 
Back
Top