Summary/Report sheet for an annual leave/attendance register

Good morning,

I was hoping I could get some advice on how to create a summary sheet for an annual leave/attendance register.

The register itself currently calculates; total staff in, total staff off, total staff and attendance for each day. It then calculates the weekly average. It formats to red when below 77%.

I would like the summary sheet to show weekly attendence % when you enter the week commencing date, I tried to reference the relevent cell but it doesn't seem to be working. I'm also hoping to be able to pull specific staff memebers details (either daily or weekly attendence) by selecting their name from a drop down list (cell A8).

I have heard that a pivot table could be used in this regard however I have been unable to get one to work. Is this possible with a formula instead?

Any help you can give would be appreciated. I have attached an example of the spreadsheet.

Hi,

Thanks for replying.

I honestly have no idea why the CF rules are in twice but I can fix that fairly easily.

I have amended the spreadsheet to give an idea of what I'm hoping to be able to do.

On the summary sheet I'm want to be able to select a member of staff from the drop down list in cell A8, in cell B8 select a date and then in Cell C8 it should show the staff members status on that date; eg. 'In', 'A/L etc.

I would also like to be able to select a particular week or even month and see the average% attendance; but I have no idea where to start. For example if the month or week was entered in cell A2 or A5 it should give the result in B2 or B5.

I've included the list of which codes count towards what on the summary sheet.

Thanks

That's amazing thank you!

The weekly or monthly % was going to be for all staff (to check if a member of staff can book any time off) but if it's possible to do it for the individual as well that would be great.

Seems to work fine but it turns out I didn't need to include details about the individual. The overall % for week and month are all we need.

You've been a great help here, thank you!