Google Sheets AL Tracker- Ind. Staff files Linked to Master

I am looking for someone experienced in Google Sheets and Google Workspace to create a simple annual leave tracking system for a small care team.

I currently use individual annual leave records for each employee. I would like to improve this so that annual leave balances calculate automatically and I can see all staff balances from a private management master sheet, while each employee can only access their own individual annual leave record.

The system needs to be straightforward for a non-technical administrator to maintain.

Individual staff annual leave record

Each employee should have their own separate Google Sheet/file containing:

Staff member name
Contracted weekly hours
Opening annual leave entitlement in hours
Holiday year
Date of annual leave
Annual leave hours taken
Additional annual leave hours added
Running balance remaining
Approved by
Date approved
Date entitlement was last reviewed

The balance remaining should calculate automatically after each transaction, so it is always clear how much annual leave the employee has left.

During the year I sometimes award additional annual leave because an employee has regularly worked more than their contracted hours. I therefore need to be able to enter, for example, +17 hours additional annual leave, and have this automatically added to their remaining balance.

Private management master

I would also like a separate master Google Sheet accessible only to management/admin.

This should automatically pull information from each employee's individual file and show, ideally:

Staff member
Contracted hours
Opening entitlement
Additional annual leave awarded
Total annual leave taken
Current balance remaining
Date entitlement was last reviewed

Changes made to an employee's individual annual leave record should automatically update the master.

Privacy/access is particularly important.

Each employee must only be able to access their own individual annual leave record. They must not be able to access the private master sheet or the records of other employees.

The system should use appropriate Google Drive/Google Sheets sharing permissions rather than relying on hidden or protected tabs for confidentiality.

I would like the finished system to include:

A reusable individual staff template
Private master dashboard
Automatic formulas/linking between individual files and the master
Clear formatting so balances are easy to understand
A simple method for adding new employees
Brief instructions for the administrator explaining how to create a new employee record and link it to the master
Advice/set-up of the appropriate Google Drive sharing permissions

I would initially like the system tested using dummy staff data before any real employee information is added.

Please let me know about your experience creating similar Google Sheets systems, particularly systems involving multiple linked files, IMPORTRANGE or equivalent solutions, and Google Workspace permissions.

I am looking for a solution that is simple, reliable and easy to maintain, rather than a complex bespoke application.