Confused on how to do a formula for my payroll workbook

[cricket1001]

I have a payroll workbook that has 26 payroll worksheets and an OT Totals worksheet at the end. I'm trying to figure out a formula in the OT Totals worksheet that will find an employee's last name in each worksheet and total their Reg OT. Last year I just made a formula by going to each worksheet finding the person's name and clicking on the cell for Reg OT. But that was a pain because we have new hires and some employees leave. Which made it difficult to update the formulas each payroll period. I have about 17, and I might need to add more, columns I need to have totals for.

Is there a formula I could use that would find a certain name, like Pat Smith, and find the cell for his Reg Ot in each payroll period's worksheet?

The employees names are in the 1st column. When I add or remove an employee it messes up the cell reference that was in the Reg OT Totals formula.

I hope I am making myself clear.

Thanks for any help!

 

[bobsan42]

Some VBA function can be written to achieve this.
But it is wrong data structure design and bad practice to split data in different sheets for different periods. Best way is to keep all raw data together in one long table with a couple of extra columns (e.g. date/month/year)
Then it becomes simple to make sense of the data for each period and find an easier way to present it - formulas, charts, pivot tables, etc...