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

 

[pthdz]

That formula seems to work.
I'm on w7.

 

[liveinhope]

so it accepts simpler formula =A2>A1 without giving an error message

now that you have successfully entered the formula does that data validation work ?

 

[Brian from Maui]

]I've PM'd Aladin, maybe he can chime in with the correct Dutch syntax. I think it's not spelled the same and may use ; instead of ,

 

[liveinhope]

Good idea Brian If Aldadin can't get this one then it must be truly obscure

as pthdz has been able to enter a simple formula starting with an = equals sign , the next thing I was going to suggest was to test more complex formulas (but not countifs) eg = mod(a2,5) = 1

I'm looking forward to the answer becuase its got me stumped

 

[mmmarks]

hi,

@ PTHDZ, In Data Validation, Just remove first = symbol and enter the formula :
Eg: Countif($A$2:$A$10,A2)=1

 

[liveinhope]

hi,

@ PTHDZ, In Data Validation, Just remove first = symbol and enter the formula :
Eg: Countif($A$2:$A$10,A2)=1

formula with equals sign works for me but formula without equals doesnt

Applying the =COUNTIF(G$2:G$10,G2) = 1 (ie with = sign) to G2:g10 results in g2 validation is =COUNTIF(G$2:G$10,G2) = 1 , the validation in g3 is =COUNTIF(G$2:G$10,G3) = 1 and so on to g10 =COUNTIF(G$2:G$10,G10) = 1
Using this "with equals" validation in my worksheet successfully prevents duplicate entries

formula w/o = in E e2 formula is COUNTIF(E$2:E$10,E2)=1 , e3 formula is also COUNTIF(E$2:E$10,E2)=1 and so on to E10 COUNTIF(E$2:E$10,E2)=1
when I enter a numeric value in the first cell E2 THE "value you entered is not valid. A user has restricted values that can be entered into this cell " message comes up

 

[pthdz]

Hey guys,

Thanks for all the replys!

@liveinhope: I can succesfully enter the simple formula =A2>A1 and the validation works. The "more complex formula" = mod(a2,5) = 1 will not work. I tried entering the formula in cell A2 and in the range of A2 to A5 (I'm not sure if this is how that one works?)
@mmmarks: I can enter the formula without the equals sign (=), but it doesn't work. Any value I enter in either one of the A-cells gives me the same error as liveinhope described above this post.
@Brian from Maui: I can succesfully enter the formula using (; ) instead of (,). It then looks like this =COUNTIF(G$2:G$10;G2) = 1. The problem, sadly enough, does remain. Any value I enter in Cell G2 to G10 gives me the same error.

I was thinking, maybe someone could send me an Excel file already made up with the type of data validation i'm looking for, so I can open it on my pc and see if that works. Good idea?

Thanks again for your time!

 

[Brian from Maui]

Sorry for the delay, slight time difference. The Dutch equivalent of COUNTIF

=AANTAL.ALS($A$2:$A$20;A2)=1

 

[pthdz]

Brian from Maui (and I assume Aladin?), you're my hero(es)! It works!

I can't express how thankfull I am.

Don't matter the time difference, it is how it is

To everybody who took time to help me out, I want to thank you with all my heart!

I'm gonna look into getting me an english version of Office so this problem never occurs again...

Keep up the good work!

Sincere regards,

pthdz

 

[liveinhope]

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 ?