Attendance Point Tracking Help!

Hello everyone! I realize there are threads already with this type of question, however I have more complex rules for the accumulation and fall off of points than others have. I figured getting this automated in excel is possible, but I don't know enough within excel to make it possible. As it is, I already borrowed an excel sheet from this group and tweaked it to get what I need. but its still not there.

The link below is the form I'm working on. the rules of point accumulation and drop off are:

90 days without any absence (even excused) -.5 dropoff
180 days without any absence (Even excused) -1 drop off.
365 from incident, regardless of any absences, point drop off.

Points/Events:
MP - Missed Punch - .25 points
LE-4 - Left less than 4 hours early - .5 point
LE+4 - Left more than 4 hours early - 1 point
MM - Missed Meeting - .5 points
A - Absent No PTO 1 day - 1 point
A+1 - Absent beyond 1st day without a doc note - .5 point
L-4 - Late less than 4 hours - .5 point
L+4 - Late more than 4 hours - 1 point
NCNS - No call no show - 2 points.
EA - Absent Excused - 0 points
EL - Excused Late - 0 Points
ELE- Excused Leave Early - 0 Points


With the more complex fall off rules, is this even possible? Help!

Hi cbrauns.
It looks to me that you already have everything set with the exception of the data validation range in the REASON column on the Day by Day tab. Select the DATA tab at the top of the ribbon, then select cell c5 through c45. In the ribbon select the upper portion of the Data Validation Icon (or if you select the Data Validation drop down, select Data Validation...) In the popup box under Source: you will see =legend!$A$2:$A$7. That is a reference to the cells on the Legend tab. Your new "dropoffs" span to row 13 now so change the formula to =legend!$A$2:$A$13 say ok. It will adjust the range for all of the validation boxes in Column C, which you can see when you go to select a reason. You may need to scroll down within the selection box to see all of them. The rest of the calculations look good, but you will need to verify that yourself.

Squeaky.