Hi,
I have a really long list of data that I'd like to summarize into a Master file.
The long list of data I have is like this:
<tbody>
</tbody>
What I'd like is to have a Master sheet automatically capture the following data:
<tbody>
</tbody>
So basically, all the months are added together, and multiplied by the cost rate in the same row to create an overall project cost table.
After which I can sort and filter as required.
Any assistance would be greatly appreciated as I have no idea where to start with this!!!
I have a really long list of data that I'd like to summarize into a Master file.
The long list of data I have is like this:
Project Number | Project Name | Project Type | Project Status | Staff Name | Week Ending Date | Hrs | Base Cost Rate | TS Code |
1 | Alpha | Absence | On Going | Adam | 01/11/2015 | 8 | 20 | Core Time |
1 | Alpha | Absence | On Going | Barry | 01/11/2015 | 8 | 25 | Core Time |
1 | Alpha | Absence | On Going | Charlie | 01/11/2015 | 8 | 30 | Core Time |
2 | Beta | Holiday | On Going | Adam | 01/09/2015 | 10 | 20 | Core Time |
2 | Beta | Holiday | On Going | Adam | 08/09/2015 | 20 | 20 | Core Time |
2 | Beta | Holiday | On Going | Charlie | 01/10/2015 | 5 | 30 | Core Time |
2 | Beta | Holiday | On Going | Charlie | 08/10/2015 | 0.5 | 30 | Core Time |
3 | Gamma | Fee | Completed | Adam | 25/10/2015 | 40 | 20 | Core Time |
3 | Gamma | Fee | Completed | Adam | 01/11/2015 | 30 | 20 | Core Time |
3 | Gamma | Fee | Completed | Adam | 08/11/2015 | 20 | 20 | Core Time |
3 | Gamma | Fee | Completed | Adam | 15/11/2015 | 25 | 20 | Core Time |
3 | Gamma | Fee | Completed | Adam | 22/11/2015 | 40 | 20 | Core Time |
3 | Gamma | Fee | Completed | Adam | 29/11/2015 | 17.5 | 20 | Core Time |
3 | Gamma | Fee | Completed | Adam | 06/12/2015 | 23 | 20 | Core Time |
3 | Gamma | Fee | Completed | Adam | 13/12/2015 | 40 | 20 | Core Time |
3 | Gamma | Fee | Completed | Adam | 20/12/2015 | 35 | 20 | Core Time |
4 | Delta | Fee | On Going | David | 25/10/2015 | 40 | 30 | Core Time |
4 | Delta | Fee | On Going | David | 01/11/2015 | 10 | 40 | OT 1 |
4 | Delta | Fee | On Going | David | 08/11/2015 | 10 | 40 | OT 1 |
4 | Delta | Fee | On Going | David | 29/11/2015 | 5 | 55 | OT 2 |
4 | Delta | Fee | On Going | David | 06/12/2015 | 5 | 55 | OT 2 |
<tbody>
</tbody>
What I'd like is to have a Master sheet automatically capture the following data:
Project Number | Project Name | Project Type | Project Status | September | October | November | December | Total Project Cost |
1 | Alpha | Absence | On Going | - | - | 600 | - | 600 |
2 | Beta | Holiday | On Going | 600 | 165 | - | - | 765 |
3 | Gamma | Fee | Completed | - | 800 | 2,650 | 1,960 | 5,410 |
4 | Delta | Fee | On Going | - | 1,200 | 1,075 | - | 2,550 |
Total Monthly Costs | 600 | 2,165 | 4,325 | 2,235 | 9,325 |
<tbody>
</tbody>
So basically, all the months are added together, and multiplied by the cost rate in the same row to create an overall project cost table.
After which I can sort and filter as required.
Any assistance would be greatly appreciated as I have no idea where to start with this!!!
Last edited: