Error Trapping

[paulstan]

Hi

Hopefully I can explain things with the aid of a diagram and words:


Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Hrs</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Job 1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Job 2</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Job 3</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">other tasks</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">0:00</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">0</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">0</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">0</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">0:00</TD><TD style="BORDER-LEFT: black 1px solid">Monday</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">0:00</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">0</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">0</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">0</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">0:00</TD><TD style="BORDER-LEFT: black 1px solid">Tuesday</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ff0000; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">0:00</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ff0000; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">5</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ff0000; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">0</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ff0000; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">0</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ff0000; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">0:00</TD><TD style="BORDER-LEFT: black 1px solid">Wednesday</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">5:45</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">5</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">5</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">5</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">4:00</TD><TD style="BORDER-LEFT: black 1px solid">Thursday</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">5:00</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">44</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">0:00</TD><TD style="BORDER-LEFT: black 1px solid">Friday</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">hrs worked in each day</TD><TD style="BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">Number of Job 1 completed</TD><TD style="BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">Number of Job 2 completed</TD><TD style="BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">Number of Job 3 completed</TD><TD style="BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">time spent on other tasks</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD></TR></TBODY></TABLE>
The above has the following validation:

• A5:A9 - =AND(A5>=0,A5<=0.5)
• B5:D9 - =IF($A5=0,B5=0,B5=INT(B5))
• E5:E9 - =AND(COUNT(A5),E5>=0,E5<=A5)

(and a huge thanks goes to T Valko (Biff) for all his hard work in helping me out).

Hopefully, I can now explain in plain English what is required!

Cols A & E contain time formats and B,C,D are numeric entries. My problem at the moment is that if someone enters a time in Col A and enters numeric values in B,C, or D then, if they go back to Col A they can change the value to 0:00 (I have done this on row 7 highlighted in RED). This leaves me with the problem of someone doing no hours work in a day but producing work (amounts in B,C,D).

The error validation the Biff supplied me with works great but it cannot trap this one error.

Anyone able to lend a hand?

Regards

Paul S

 

[mikerickson]

Add a condtion like AND(A1=0,SUM(B1:E1)>0)

 

[paulstan]

Thanks for the quick reply.

Leaving myself wide open here, but can you tell me where to put it (in relation to the other data validation as mentioned)?

Regards

Paul S

 

[mikerickson]

A5:A9 would become =AND(AND(A5>=0,A5<=0.5), AND(A5=0,SUM(B5:E5)>0))
B5:D9 - =AND(AND(A5=0,SUM(B5:E5)>0), IF($A5=0,B5=0,B5=INT(B5)))
E5:E9 - =AND(AND(A5=0,SUM(B5:E5)>0), AND(COUNT(A5),E5>=0,E5<=A5))

 

[paulstan]

Again, many thanks for the quick reply.

I copied exactly what you provided into appropriate cells, but now I get error messages anytime I try to enter data in any cell!!

Regards

Paul S

 

[T. Valko]

A5:A9 would become =AND(AND(A5>=0,A5<=0.5), AND(A5=0,SUM(B5:E5)>0))
B5:D9 - =AND(AND(A5=0,SUM(B5:E5)>0), IF($A5=0,B5=0,B5=INT(B5)))
E5:E9 - =AND(AND(A5=0,SUM(B5:E5)>0), AND(COUNT(A5),E5>=0,E5<=A5))

The OP is saying that once the data is initially enterd the validation works according to the established rules.

If the data needs to be changed then some of the validation no longer works.

I'm pretty sure they need a Worksheet_Change event macro to do this.

I'm not much of a programmer so...

 

[mikerickson]

It should be simple enough with native Validation to forbid entry into any cell unless col A is filled. (A would have an additional condition that it be a time. eg TimeValue(Text(A1,"hh:mm:ss")) > 0) that would prevent the user from entering 0 into column A at any time.

(I'm doing this on the fly and haven't looked back to the pre-this-thread conditions yet.)

 

[T. Valko]

It should be simple enough with native Validation to forbid entry into any cell unless col A is filled. (A would have an additional condition that it be a time. eg TimeValue(Text(A1,"hh:mm:ss")) > 0) that would prevent the user from entering 0 into column A at any time.

(I'm doing this on the fly and haven't looked back to the pre-this-thread conditions yet.)

It would be the same effect as having dependent drop downs.

A1 has a drop down for makes of cars. You select Chevy.
A2 has a dependent drop down for the models based on the make. You select Corvette.

You then change your mind and from the drop down in A1 you select Mazda.

A2 still displays Corvette which is an invalid model for Mazda.

This is basically what the OP is saying!

 

[paulstan]

Further to Col A not containing a 0 - it is possible that all Cols (A-E) could be filled with 0s, when a person doesn't work on a particular day. All Cols will initially be pre-populated with 0s.

Biff's (T Valko) analogy regarding cars/makes/models is spot on.

Thank you for your time regarding this little oddity!!

Regards

Paul S

 

[mikerickson]

It would be the same effect as having dependent drop downs.

A1 has a drop down for makes of cars. You select Chevy.
A2 has a dependent drop down for the models based on the make. You select Corvette.

You then change your mind and from the drop down in A1 you select Mazda.

A2 still displays Corvette which is an invalid model for Mazda.

This is basically what the OP is saying!

That's on approach. And it would need VB.

Another approach is
Try to enter tasks in non-A column, be told "You must enter your time"
Enter time in A
Enter tasks in other columns
Enter 0 in A and you are told "No, you must be mistaken about those hours"

Which could be done with Validation.

I guess it would have to do with how one views the spreadsheet. Is it a log of what you did that day "I did tasks A, B, C and worked a total of X hours"?
Or is it an accounting of those X hours. "My X hours were divided between tasks A, B, C"?
Same data, but how does the user view the report?