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.

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.




<(E-Mail Removed)> wrote in message
news:e71d72e7-5fe9-4f52-aa7e-(E-Mail Removed)...
>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.

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.

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

On Jul 10, 10:12*am, "Skinman" <ask...@optusnet.com.au> wrote:
> 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...