I'm creating a workbook to use for payroll. My employees are paid to grade exams, and the number of exams they grade is listed on individual worksheets per test date, which includes other information like which specific exams they grade for that test date, etc. The date on which they actually grade the exams (which is sometimes different than the day of the exam itself) is listed underneath the total number of exams graded for each test date.

I'd like to create a formula on the "Payroll" worksheet that looks through all the worksheets in the workbook to see if the date they did grading on is included in that pay period, and then return the number of exams they graded on the Payroll sheet so I can total their paycheck, as sometimes there's more than 1 exam during a pay period. Is there a way to use the LOOKUP function to do this? I have a basic understanding of the function but haven't used it very often. Also, if there is a way to use the LOOKUP function across all worksheets, will this include new worksheets that are added later? I copy the test day sheet and add it to the end for each new test date, but I'd like the function to look through any new sheets added in the future as well.

I appreciate any advice or help. Thanks!