Gradebook & Attendance book. Not sure if I need to use an IF, COUNTIF or VLOOKUP function

[mrobscure]

Hi

I am trying to make an Excel workbook that will serve me as an attendance and gradebook for my classes.

I've amended an online template and nearly got it doing what I want. Except now I've run into a problem.

Students get 2 participation points each class but if they are late they lose 1 and if they are absent they lose 2.
I want my Gradebook worksheet to check from the Attendance worksheet to see which students are late or absent and then adjust the participation points accordingly.

Also, if students are generally misbehaving in class or they haven't done their homework they could lose 1 or 2 of the points. This I don't mind manually adjusting, as this situation isn't that common.

I am using Excel 2007

GradebookandAttendance.xlsx