need help w/ data validation problem
Posted by Kevin on October 02, 2001 11:50 AM
We have a spreadsheet file at work in Excel 2000, that contains a column with serial numbers. We have data validation turned on in that column to only allow a number to be entered once. If you try to enter a duplicate number, you get an error message. The problem is that every once in a while, we will run into a situation where we type a number in and get the message that this is a duplicate and prompts us to change the number to something else, but when we do a search to try and find the number, there is no such number in the spreadsheet. Does anyone know why it is telling us that the number is a duplicate if it is the only number of its kind in the sheet?
Thanks in advance,
Kevin
Posted by Mark W. on October 02, 2001 12:37 PM
What validation criteria are you using? (nt)
Posted by Kevin on October 02, 2001 1:41 PM
Re: What validation criteria are you using? (nt)
In Data-Validation, I am using the custom validation, and inserting the following formula: =COUNTIF($A$1:A1,A1)>1
This formula counts the duplicates in a range and if the result is greater than one (i.e. there is a duplicate) it returns the validation error msg.
Why isn't this working though?
Thanks,
Kevin
Posted by Mark W. on October 02, 2001 2:07 PM
Re: What validation criteria are you using? (nt)
Kevin, the formulas used for validation criteria
should evaluate to FALSE when invalid data has
been entered. Your formula evaluates to TRUE
with multiple entries of the same value. Try
using...
=COUNTIF($A$1:A1,A1)<2<p>
Posted by Kevin on October 03, 2001 5:11 AM
That did the trick - thank you
