Data Validation

[HarryisTrying]

I thought I had this one figured out. I have a column containing either NOT
TAGGED or a unique series of characters.
I put this in the next column =IF(A2="NOT TAGGED", "NOT
TAGGED",COUNTIF($A$2:$A$500,A2),=1)
and it works great.

So I went to the column where I want the validation and the first cell. I
clicked on Data Validation (Excel 2007) thenSettings then picked Allow then
Custom and then in the formula bar put the formula above.

I got no error but it finds NOT TAGGED as a duplicate when it is entered in
a cell. If I use the countif part only it prevents duplicates. However, I
want to allow duplicates of NOT TAGGED and thought the IF statement should
take care of that (and it does if the formula is in the next column) but it
will not work using the Data Validation icon process.

Why?

 

[HarryisTrying]

I didn't put the < at the end of the countif; here is the formula I am using
=IF(A2="NOT TAGGED", "NOT TAGGED",COUNTIF($A$2:$A$500,A2),<=1)

 

[ker_01]

Two comments;

First, you have too many parameters in your formula. The IF can only take
three parameters- you are splitting your third parameter with an unnecessary
comma.

Second, although I haven't tested this, I suspect that the validation
formula needs to return either a true or false value. If this is the case,
your formula might be something more like:

=IF(A2="NOT TAGGED",TRUE,COUNTIF($A$2:$A$500,A2)<=1)

HTH,
Keith

 

[HarryisTrying]

thanks for your thoughts. I am now trying to write a function or macro to do
the job.

 

[HarryisTrying]

I figured it out. Here is the correct formula
=if(a2="not tagged",TRUE,countif($a$2:$a$2000,a2)<=1)

I had Not Tagged repeated but should have put TRUE there so if it found a
Not Tagged it would evaluate to true.