Data validation... Help needed!

[pthdz]

Hello,

I'm making an excel database with email adresses and phone numbers. I wish to receive an error alert whenever a phone number has already been put in the database.
For this, I use these steps:

Select cells (A2 until A20, for example).
Go to datavalidation -> select custom
use this formula: =COUNTIF($A$2:$A$20,A2)=1
It then prompts me I can not use '=' so I delete the equal sign (is this normal?)
I test the first cell and any number or value I typ in, it gives me an error alert.





I do not understand what is going wrong. I tried installing an older version of excel because I thought it might be some bug, but it's the same result... I use Office 2010 atm.

Help pls!

Regards,

pthdz

 

[Brian from Maui]

Well thats my "learn something new every day" for today. I didnt realise that comma was not a universal separator . (your post that the =MOD(A2,5) =1 didnt wrok either was a good clue along those lines

pthdz now that you know to use ; instead of , in your Dutch version you should be right .re your request to attatch a simple file illustrating this COUNTIF validation at work I'm not sure how to do that

Brian from Maui - are you able to do that ?

See Peter's reply here

http://www.mrexcel.com/forum/about-board/508133-attachments.html