Hi,
I need some help please, I’m OK at basic excel, but I’m not sure how to achieve what I need to do or even if it can be done!
I have a two worksheets in the same workbook (2010):
- Resource Profile
- Budget Tracker
The Resource Profile lists all of the resources for a particular project. Starting from Column A / Row 4:
Col A = Role, Col B = Name, Col C=Workstream, Col D=Resource Type, Col E=Daily Rate, Col F=Blank, Col G=Apr-14, Col I= May-14, etc …. To Mar 16.
Resource type will either be:
- FTE
- External
The Budget Tracker is the financial breakdown for a project with the following columns:
Col A=Blank, Col B=Blank, Col C=Item, Col D=Description, Col E=Approved Budget, Col F=Blank, Cols G, H, I are merged for Row 5 with Apr-14, and Row 6 is G=Forecast, H=Actual, I=Variance. Cols J,K,L are merged at Row 5 and titled May-14 and in Row 6 J=Forecast, H=Actual, I = Variance etc.
What I need to do is enter all my resource information on the Resource Profile then all the resources copied over to the budget sheet (either via formula or macro).
If there are 10 resources on the resource sheet (the number will always change based on the project), then those resource names are copied into the Budget Tracker with the allocation data copied into the forecast column for each month as a $ figure.
So if a resource has a daily rate of $100 and they are allocated for 1 day Apr-14, 1 day in May-14, 2 days in Oct-14 then in the budget tracker against that persons name (appearing in the Item Column) there will be $100 in the Apr-14 Forecast Column, $100 in the May-14 Forecast Column and $200 in the Oct-14 Forecast Column.
Ideally, based on the Resource Type (either FTE or External) they would also appear under that section in the Item Column on the Budget Tracker sheet.
Even as I type this it all sounds like so much work, so I’m not even sure if it’s possible or worth it, but all assistance greatly appreciated!
Thanks
Dave.
I need some help please, I’m OK at basic excel, but I’m not sure how to achieve what I need to do or even if it can be done!
I have a two worksheets in the same workbook (2010):
- Resource Profile
- Budget Tracker
The Resource Profile lists all of the resources for a particular project. Starting from Column A / Row 4:
Col A = Role, Col B = Name, Col C=Workstream, Col D=Resource Type, Col E=Daily Rate, Col F=Blank, Col G=Apr-14, Col I= May-14, etc …. To Mar 16.
Resource type will either be:
- FTE
- External
The Budget Tracker is the financial breakdown for a project with the following columns:
Col A=Blank, Col B=Blank, Col C=Item, Col D=Description, Col E=Approved Budget, Col F=Blank, Cols G, H, I are merged for Row 5 with Apr-14, and Row 6 is G=Forecast, H=Actual, I=Variance. Cols J,K,L are merged at Row 5 and titled May-14 and in Row 6 J=Forecast, H=Actual, I = Variance etc.
What I need to do is enter all my resource information on the Resource Profile then all the resources copied over to the budget sheet (either via formula or macro).
If there are 10 resources on the resource sheet (the number will always change based on the project), then those resource names are copied into the Budget Tracker with the allocation data copied into the forecast column for each month as a $ figure.
So if a resource has a daily rate of $100 and they are allocated for 1 day Apr-14, 1 day in May-14, 2 days in Oct-14 then in the budget tracker against that persons name (appearing in the Item Column) there will be $100 in the Apr-14 Forecast Column, $100 in the May-14 Forecast Column and $200 in the Oct-14 Forecast Column.
Ideally, based on the Resource Type (either FTE or External) they would also appear under that section in the Item Column on the Budget Tracker sheet.
Even as I type this it all sounds like so much work, so I’m not even sure if it’s possible or worth it, but all assistance greatly appreciated!
Thanks
Dave.