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

 

[paulstan]

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?

This part of the spreadsheet is just logging tasks achieved against hours worked. Another part will work out percentages of each task against hours worked.

For ease of use for the end-user, it would logically dictate that they completed hours worked column (Col A) before completing the task columns (Cols B,C,D) and then finally entering any other tasks (Col E).

Regards

Paul S