data validation not working properly

[CCNProjects]

hello.

i am trying to prevent users from entering duplicate keys.
data, validation, allow=custom, formula=COUNTIF($B$14:$B$30,B27)=1

i get a message error when i enter a duplicate key, but when i copy and paste a value that already exists, i do not get a message error and the value is accepted by excel.

what should i do?

thanks.

 

[xlbo]

when you copy and paste the value, does the cell you are copying from have the validation set up on it ??

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Copying is not the same as entering, so will not trigger an error message. To see errors like this highlighted, use the Auditing Toolbar and click the Circle Invalid Data button.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.