Compiling from multiple sheets

[Eyowzitgoin]

This may be long but I'll try to keep it short.

I'm a teacher who has multiple students coming in for classes at all hours of the week. Some come in for 2 hours one day a week (which counts as 2 classes) and some come in 2 days a week for 1 hour a day and so on. To keep track of this, I've created an attendance workbook with the students names listed under the hour they come in for class. In the cells to the right, I have the date their classes end and in the following columns of the same row,their attendance. The end date can change if they have a valid reason for not attending class so I've worked out the calculations to change their end date based on their attendance. Since I have students on 6 days a week, I have a different worksheet for each day of the week (otherwise, things get super messy).

I have worked out how to calculate the new date by adding weeks according to the attendance code. However, this only affects the one date. Since some students come in multiple times a day or multiple weeks, this creates a problem. I had worked it out so that if the students' name appears on one sheet twice, the second attendance figure was ignored but this leaves my students who come in 2 days a week with problems.

I thought I would create a new table of all names on a helper sheet, combine the attendance from all instances of each name across the workbook, calculate the new date and then feed it back into the right spot on the schedule. But I'm having trouble creating the list of names.

The code I have devised after reading a few posts and tweaking what I saw for my needs (and my Excel03 version) I believe would work but for some reason the calculation just ends midway - no error code, no circular logic problems, just stops calculating (perhaps too many calculations?). Here's the code I have devised:

=IF(ISERROR(INDEX(Names,MATCH(0,IF(LEN(Names)>0,COUNTIF(Names,"<"&Names),-1)-SUM(COUNTIF(Names,$A$200:A201)),0))), "", (INDEX(Names,MATCH(0,IF(LEN(Names)>0,COUNTIF(Names,"<"&Names),-1)-SUM(COUNTIF(Names,$A$200:A201)),0))))

"Names" is the range I defined to mean column B from worksheets titled Monday - Friday. I think this may be the cause as the code seems to work fine for a range that I input directly and is on the same worksheet.

Any help would be greatly appreciated.

And since I'll be doing this immediately afterward, if anyone cares to help me figure out how to get the corresponding start date from the first instance of each name in the new list, that would be nice too but not really needed at this point.

Cheers!