Instruction for timekeeping file

Attached file is my question for the time keeping file.

In my time keeping file, I have 2 sheets. The first one is working time sheet, this sheet is used for recording the working day and leaves of staff.
Detail of Working and leaves day will be showed in staff sheet.
I want to have a formula which can link from staff sheet to the timesheet sheet for each staff at each day. Such as:
22 is working day of Pete and in timesheet sheet, the X will be fill on F5
21 is Sunday in the timesheet it will be empty
25 is compensation day, in timesheet it will be fill full with CP on I6
26 is annual leaves, in timesheet it will be fill full with AN on J6
29 : no Oder day = NO on M6
This will be recorded the same for Nancy’s row in timesheet sheet.
Do I need to have a VBA code for this job or I just need a formula?

Kindly give me the instruction for this case.
Thank guys.

Hi Jenny

Please see attached for one solution. I had to change the date formats on the individual time-sheets to match that of the summary time-sheet in order to make the lookup formulas work but it seems to be working okay.

The formula required is as follows:

=INDEX(Pete!$I$10:$I$19,MATCH(F$5,Pete!$B$10:$B$19,0))

Hope this helps

HI Jeversf,
Thank you for quickly intrust,
This formula is ok for my file now
Thank you alotd\

Originally Posted by Glenn Kennedy

With a Table on sheet 1, assigning the abbreviations to each phrase, this formula will do it:

=IFERROR(VLOOKUP(VLOOKUP(DAY(E$5),INDIRECT("'"&$D6&"'!$B$9:$C$19"),2,FALSE),Sheet1!$A$1:$B$6,2,FALSE),"")

in E6, copied across and down. You will need to manually adjust the ranges $B19:$C$19 in your real sheet. This solution will work for ALL people who have timesheets, WITHOUT modification.

HI Glenn,
Thank you so much for your solution. My file is really good with your formula without any modification