Data validation... Help needed!

You have a number of options, but I don't think data validation will work the way you want it to.

The simplest would be to have a column next to the phone numbers, and have it say DUPLICATE if a duplicate is entered. See the following example:

in cell b2 the formula is:
=if(countif($A:$A, A1)>1, "DUPLICATE!", "")
drag that formula down.

The other option would be to write a vba macro that runs every time data is entered, searching for a duplicate value and displaying an error message if one is found. Let me know if that sounds more to your liking.

Hey,

Thanks for your reply!

Having the word 'duplicate' popping up in the next cell could work. It's far from perfect, but it might just do the trick!
However, I don't really understand how that formula works and I also don't understand where to enter that formula.
Given that A1 to A10 are my phone numbers. Do I put the formula in B1 to B10 or...?
I'm sorry, I'm pretty noob when it comes to Excel.

+ I've seen tutorials on the internet perfectly showing me what I need. It just doesn't work for me like it's shown in the tutorials... I also can't put a '=' sign before my formula. It gives me an error whenever I try doing that, telling me to delete the 'equal sign'. That behaviour looks abnormal to me, but I don't know...
I could give links to some of the tutorials I tried but I'm not sure if that is allowed on this forum, so before I get into "trouble", I rather ask first

Thanks again for your reply!

Regards

what version of excel are you on ? you say you can't get excel to accept formula =COUNTIF($A$2:$A$20,A2)=1

well I am on 2007 and I have successfully entered formula =COUNTIF($C$2:$C$10,C2)=1 which is the same as yours (except that yours works on Col A , min on Col C)

And the formula works ... so if you are on 2007 or later you should be able to do this too
not sure about earlier versions

Hey liveinhope,

Thanks for your reply!

I'm using Office 2010 at the moment. I already said that in my original post
I have tried using 2007 as well, but with the same result.
I'm pretty sure what I'm doing is correct, and it should work, but it doesn't... I have tried many things but all without succes!

Can anyone explain why Excel doesn't let me put the equal sign (=) in front of my formula's? Could it be that this is some sort of bug? It feels like a bug, but I don't know enough about Excel to be sure... Maybe it's some sort of setting that doesn't allow the formula's to work?

Anyone?

I'm stumped here - as I said I can enter formulas such as =COUNTIF($A$2:$A$20,A2)=1 in excel 2007

and I'm not aware of excel options that would explain why I can do this but you cant

silly question #1 - just make sure you arent entering 2 equals signs such as = =COUNTIF($A$2:$A$20,A2)=1

another question - what error message , if any, do you get when you try to enter a formula with equals sign

What language version are you using? Your screenshots aren't in English

@brian from Maui: It's a Dutch version. I have tried an english version of Excel 2007 with the same result.

@liveinhope: I'm not putting 2 equal signs

Error message translated is something like this:

The formula you have entered contains an error.

- Click on more info about blablabla for formula's.
- If you need help click on the function wizard blablabla.
- If you are not trying to enter a formula, it's best not to use the equal sign (=) or the minus sign (-) or place a single quote (') in front of it.

very strange - I'm glad BrianfromMaui has joined in this thread cos I'm pretty weel out of ideas except for

and I know it's not he formula you want in the end but try for the moment a simpler formula =A2>A1

what is your OS (Im XP)