How to create bonus calculation for a second employee

Hi
hope everyone is well. I think I should just get right to it, as I feel there is a lot of explaining to do (for me anyway)

I have a team of two, who don't always work together, but generally do. The target for the team for the week is £5750 and there is bonus paid to the lead if the team attracts bonus, dependent on whether or not the lead has taken any unpaid leave or sick that week. So if there is no sick and the target is achieved the lead will receive £75 and then for every £1.00 of work done over the £5750, the lead is paid 10%. So for a lead working 5 days and total work for the week is £6750 - £5750 = £1000.00 and 10% is £100.00, the lead would take home £175.00.

I have a group of spreadsheets which work this out. Now the manager wants to also pay the bonus to the second in the team (the lead's assistant). He wants to pay the lead 75% of the bonus and the assistant 25%. I will need to go through a HR consultation process of course, but if it is agreed I will need to be able tow work this all out.

As I progress I find that it is actually more difficult than it looks

1. I now need to take into account the sickness and annual leave and unpaid leave accordingly for the assistant to get their bonus also.
2. I need to find the assistant each day on a table which is called the installer master.
3. Each day is on a different tab
4. The assistant could be working with any lead

So the name of the assistant needs sourced each day, and then the work address, and values of the job (job type and price) all need to be brought onto a new tab, so that I can work out the bonus bit, all this will be sourced from 8 tabs in the workbook

I have uploaded the workbook, I apologise about the size of it and I am hoping that you can tell me what type of formulae to use, or any other help appreciated.

I can't sort the information, it is not in a list.

Many Thanks for your time.

Tanya

Welcome to the forum.

Is this a formula or VBA query?

Thanks. I was just seeking clarification, as it's a macro-enabled workbook. It's very big and busy - do we really need the whole thing in order to help? Can you provide a cut-down sample? Don't worry if not, but you will need to provide some signposting! Have you included mocked up results in the workbook? If not, please add them manually where you want to see them.

Thanks.