Data Validation - Excel

[debbielee26]

Hello,

I'm trying to use the data validation function on EXCEL 2000 to help prevent duplicate entries but something is wrong with my formula.( I think that's the problem) I get the error message to pop up but the problem is that the error message pops up everytime I make an entry. What did I do wrong??

Thanks,
Debbie

 

[SkipVought]

Hi,

How are you validating your data -- need more SPECIFIC information.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

and the formula you are currently using would be nice ;-)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[xlbo]

but being as I remembered this coming up before, if you are checking for duplicates in the same column, use
validation>custom
and enter
=COUNTIF($A:$A;$A1)=1

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[debbielee26]

The following tip did not work:

validation>custom and enter =COUNTIF($A:$A;A1)=1...It's giving me the same error.

I'll try to be more specific and maybe this will help. I have all invoice numbers listed from column A2 to A900. THe invoice is listed from A2 because I have the title Invoice Number on A1.This list will always be continuous since I'll be adding more invoice numbers as time goes. Currently, I am doing CTRL + F to check duplicate entries but it is very inconvenient. The original formula I tried was validation>custom and enter A<>A.(<> is the not equal sign)

Before I do the validation, do I need to highlight the rows I want to validate?

Please HELP and thank you for trying...

 

[xlbo]

ok
select A2:A900
Data>Validation
=COUNTIF($A:$A;A2)=1

tested and works

You'll need to apply the validation to any new entries as well (or set it up so the validation is applied to cells below the data set)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[debbielee26]

Hello,

I tried doing as told but the formula =COUNTIF($A:$A;A2)=1 is not correct. I copied and pasted formula in formula box and this is the error message I get. &quot;The formula you typed contains an error.&quot;

Therefore I tried without the equal sign on the beginning and I had no error message pop up but I when I tested, same thing happened as before. I got the error alert that I made when entering any invoice number.(duplicate and new invoice numbers)

THanks,
Debbie

 

[SkipVought]

Hi,

Try enterign WITHOUT the semicolon -- use COMMA!...

=COUNTIF($A:$A,A1)=1

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[fkegley]

Try =countif($A:$A,A2)=1. I tried this and it works!!!

Frank kegley
fkegley@hotmail.com