Tally sheet, reseting parts once a week but keep running totals

Ok, in my sheet, this is what needs to happen (in my mind of course):

First, ignore the colored portions. This is how I kept track of what I had completed in the form as I created it. Clients attend class once a week and pay once a week for their classes.

Top column: Report for week (every Monday's date)

First Row = Name of client (this same name when entered in the "5groups" sheet, will automatically be entered into the corresponding class sheet)

Second Row = I will enter the number of classes required by the court

Third Row = when they attend their class, they will be marked "present" in the correct sheet, thus adding +1 to their classes attended so far. I need the number of classes to become cumulative; therefore every time they are present in class, it adds 1 to this column (at the bottom of this explanation, you will see how this would ideally happen without adding additional sheets).

Fourth Row = Classes required minus classes attended equals classes remaining

Fifth Row = When they make a payment, this number should increase accordingly so we can track how many classes they have paid for

Sixth Row = Nothing fancy, this is their probation number

Seventh Row = We will enter the fee due for each class manually based on their income level and fee tier

Eight Row = Minimum payment due:

o When "amount paid" equals "payment per session" set "minimum payment due" to $0 and increase "classes paid" by +1

o When "amount paid" equals less than "payment per session" set "minimum payment due" to (what the remaining amount is) and do NOT increase "classes paid" until they catch up on payments to equal $0 on "minimum payment due" and then the above bullet would happen (only increase classes paid by +1 when the amount paid = payment per session) Example: Johnny has to pay $10/session. He does not pay this week or next week. Minimum payment due is now $20. He pays $15 and still owes $5. Minimum due = $5, but classes paid increased by 1 because he met the $10/session minimum. When he pays the remaining $5, classes will increase by 1.

o When "amount paid" equals greater than "payment per session" set "minimum payment due" to reflect the [credit] and increase "classes paid" accordingly

Ninth Row = the amount they are paying this week (we enter this number upon receipt of payment)

Special Notes:

Every Monday the date of "Report for week beginning" to reflect that Monday's date.

Every Monday the "minimum payment due" should reset to the same as "payment per session" without affecting the rest of the columns. If there is a balance due, that would be added, if there is a credit, it would be deducted

Every Monday the roster sheet resets, without changing the “classes attended” column total and continuing to keep count.


SoOOoOooOooOooOoOoooooOOOOOO...............should I go back to one step at a time?