Copying Various Data from one Sheet to Another

qdavhen

New Member
Joined
Feb 16, 2007
Messages
9
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.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

jerH

Board Regular
Joined
Dec 3, 2008
Messages
168
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

New Member
Joined
Feb 16, 2007
Messages
9
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

Board Regular
Joined
Dec 3, 2008
Messages
168
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

New Member
Joined
Feb 16, 2007
Messages
9

ADVERTISEMENT

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

Board Regular
Joined
Dec 3, 2008
Messages
168
How many groups are there? Would it be feasible to let each of them have their own sheet? That might make it easier...
 

qdavhen

New Member
Joined
Feb 16, 2007
Messages
9
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,653
Messages
5,626,105
Members
416,161
Latest member
David1966Lewis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top