Formulas to show dates out of attendance

[shakes347]

I have attached an excel document that has two sheets.

Sheet one (DP): a list of classes that a students is enrolled in

Sheet two (CSC): a form that is connected to sheet one that shows which classes have been added and dropped

What I need:

In the CSC there are 9 cells that I need to populate from the DP sheet. These cells are D30, G30, K30, D32, G32, K32, D34, G34, K34.

These cells need to show the dates that a student is out of attendance. The days that a student are in attendance are list in the DP. Normally a student is in attendance every seven days, but if a student drops a class they are obviously not in attendance for those five weeks.

Is it possible to have these cells populate with the day after the students last day in attendance (D30, 32, and 34), the day before the students next day in attendance (G30,32, and 34), and the total days out of attendance (K30,32, and 34)?

Thus, these cells would need to some how search all the dates in attendance found in the DP sheet, return the first day out of attendance and the last day out of attendance as well as the total days out of attendance.

One thing that I have done so far that may be helpful (or not)...If A1 is entered anywhere in A21-34 then the CSC is populated with the correct data from that row.

This is driving me crazy and I am not at all sure it is possible, nor do I know if I have explained myself clearly.

Thank you in advance for your help!