Help creating self updating weekly log of hours

Hi, bit of a conundrum I'm struggling with...

I am trying to create an excel file to keep log of 80 employees' overtime worked each week for the full year.

I have a one worksheet which lists all the personal details down the left hand side - first name, surname, payroll number. Along the top in each column is the week number starting at week 3.

I'm trying to get clever now (but failing as I'm resorting to asking for help online!) because I've made another worksheet for managers to go on which has 3 fields - employee number, number of hours overtime, week number the overtime is from.

The not so clever part is on the worksheet with all the personal details, in each cell along the row there is an IF formula that asks if the employee number input on the first sheet matches the particular one in this row, and also if the week number input on the first sheet matches that particular week number in the column at the top, and if it does to add the number of hours overtime (again from first sheet) to that cell...

Looks nifty but I have no idea how to keep the value the formula produces in that cell, because the first sheet is always cleared of all data before the next person uses the spreadsheet...

Sorry if this doesn't make sense because I struggled wording this post.

Any suggestions would be appreciated. Thanks in advance!

Alastair

Hi tuph,

I have attached a copy of the file, personal info removed and replaced with one dummy line.I have left the formula I created along "John Smith's" row and the one below on the weekly hours tab. Row 2 is also hidden which contains the numerical value of the week number for the formula to work.

Any advice would be greatly appreciated!

Thanks