Data Validation Question

[RJQMAN]

I had my worksheet working great and then I did something - I do not
know what - and data validation crashed.

My goal is to have 3 columns, each with 20 entries, with the user
entering data into columns A and column B. Column C is the total. If
the user enters data into columns A or B that causes the total in
column C to duplicate a prior entry in column C, I want to display a
warning.

Using Data Validation, I had entered the following formula into column
A as a custom validation formula

=and(countif($C1:$C20,C1)=1,ISNUMBER(B1))

In column B I had entered
=and(countif($C1:$C20,C1)<=1,ISNUMBER(A1))

I was getting an error message, which I had placed into the Warning
section of the error alert. All was wonderful. Then I did some other
things to the workbookt, and came back to this. Now nothing works.

 

[Skinman]

Try changing
=and(countif($C1:$C20,C1)=1,ISNUMBER(B1))
To =and(countif($C1:$C20,C1=1),ISNUMBER(B1))
And
=and(countif($C1:$C20,C1)<=1,ISNUMBER(A1))
To =and(countif($C1:$C20,C1<=1),ISNUMBER(A1))
COUNTIF was not evaluating properly. Hope this helps.

 

[RJQMAN]

HELP PLEASE!

Thanks for responding, but no luck. Plus this morning I when I looked
at the formula I transposed into this group late last night, I
realized I had a typo in my posting. As I continue to try to have a
validation caution message if the total in column C will be a
duplicate of a previous entry, the two formulas were;

In Column A , which did not work...
=and(countif(C$1:C$20,C1)=1, ISNUMBER(B1))
I tried your suggestion of
=and(countif(C$1:C$20,C1<=1,ISNUMBER(B1))

and Column B
=and(countifC$1:C$20,C1)<=1,ISNUMBER(A1))

but now no matter what number I enter in Column A, I get my error
message.

I do not know what I can do.
Is it possible that I cannot reference another column in a data
validation formula? Or perhaps I am trying to compare a number that
has not yet been calculated? Ugh. I really need some help, and do not
know where to turn. Thanks for trying.

 

[Skinman]

This works for me =IF(COUNTIF(C$1:C$20,A1+B1)<=1,"TRUE","FALSE")
Enter this Formula validation into Range A1:A20 and the same formula
validation for Range B1:B20
If a value is entered in A or B that totals the same as any previous total
in C it will come up with error code.
Let us know how you get on please.
Skinman

 

[RJQMAN]

This works for me =IF(COUNTIF(C$1:C$20,A1+B1)<=1,"TRUE","FALSE")
Enter this Formula validation into Range A1:A20 and the same formula
validation for Range B1:B20
If a value is entered in A or B that totals the same as any previous total
in C it will come up with error code.
Let us know how you get on please.
Skinman

That solved my problem. Thank you very much. Some day I hope I
understand it.

Warm regards from Florida USA...