Copying Various Data from one Sheet to Another

[qdavhen]

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.

 

[jerH]

Not exactly sure I follow your description of the Budget Tracker sheet. If you could reply with a couple of tables that show your layout and some example data that would help.

 

[qdavhen]

Hi, hope this helps....



Month Apr-14 May-14
Item Description Approved Budget Forecast Actual Variance Forecast Actual Variance
3rd Party Contracts
Service 1 0.00 0.00
Service 2 0.00 0.00
Service 3 0.00 0.00
Service 4 0.00 0.00
Service 5 0.00 0.00
3rd Party Contracts Total 0.00 0.00 0.00 0.00 0.00 0.00 0.00

Travel and Expenses
T&E - Supplier 0.00 0.00
T&E - Contractors 0.00 0.00
Travel Total 0.00 0.00 0.00 0.00 0.00 0.00 0.00

External Resource
Role 1
Role 2 0.00 0.00
Role 3 0.00 0.00
Role 4 0.00 0.00
External Resource Total 0.00 0.00 0.00 0.00 0.00 0.00 0.00

Internal Resource
FTE 1 0.00 0.00
FTE 2 0.00 0.00
FTE 3 0.00 0.00
FTE 4 0.00 0.00


Thanks

Dave.

 

[jerH]

It looks like there's a little more going on with the Budget Tracker sheet. The four groups (3rd Party Contracts, Travel and Expenses, External Resource, Internal Resource) are those always going to be it, or can that change? And what determines which group on the Budget Tracker an entry on the Resource Profile goes in? FTE and External seem to line up with Internal Resource and External Resource, but what about the other two? And of course, the number is dynamic, right? If I can fully scope what you're trying to do it shouldn't be too dificult to put together a macro so that you enter some data on the Resource Profile sheet, hit a button, and it copies things into the appropriate places.....

 

[qdavhen]

Thanks. There will always be the same groups (there's lots more than 4), the groups will always be there, but the content is dynamic, in that in each group there could be 1 or 100's of line entries. The order can be arranged so the resource sections are either at the start of end (if that helps). Only resource entries are in the resource tracker, whcih needs to be transposed to the budget tracker.

So, costs will be entered directly in the budget tracker for all other sections, whereas they will be entered into the resource tracker for resources and copies accross to the budget tracker. Hope this helps. Apologies for the delayed response.. I really do appreciate your efforts.

Thanks

 

[jerH]

How many groups are there? Would it be feasible to let each of them have their own sheet? That might make it easier...

 

[qdavhen]

There are 5 groups:
- IT Hardware / Software
- 3rd Party Contracts
- Travel and Expenses
- External Resource
- Internal Resource

It's not feasible to have separate tabs for each as it would make data entry convoluted. At the moment, I only have to enter data on a single tab "Form". I can send the actual file if it makes it easier, but I'm not sure how to attach or send a file via the forum.....

Thanks.