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.