Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation Question
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation Question
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. wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation Question
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation Question
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation Question
On Jul 10, 10:12*am, "Skinman" 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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation Question | Excel Discussion (Misc queries) | |||
Data Validation Question | Excel Discussion (Misc queries) | |||
Data Validation Question | Excel Discussion (Misc queries) | |||
Data Validation Question | Excel Discussion (Misc queries) | |||
Data Validation question | Excel Worksheet Functions |