Using excel to track project movements

Excellling

Board Regular
Joined
Sep 4, 2013
Messages
89
Hi all,

I am hoping for some general assistance in how I might use excel to capture ongoing movements in projects.

At the moment I have several projects all with the same fields. The projects costs are all driven by resources (days worked).

These fields include:

Name
Start date of the resource
End date of the resource
Location of resource
Part time/Full time status of the resource
Daily rate of the resource

This information is then used to derive a cost estimate and produce forecasts etc.

Project managers submit regular changes to their resources which are updated and a new cost is determined.

What I wish to do is be able to track these changes when they occur and automate the spreadsheet so that it keeps a running record of each version of updates.

So my cost may start at say $100. Updates come in and now the forecast for the year is $105. Then more updates and now $115. Then down to $110 etc.

It would even be handy to be able to categorise these cost drivers.

For example: started at $100, now $115. Driven by $5 of new resources, $5 resources starting earlier, $5 resources ending later or whatever.

I know this is a very generic question, but just trying to get an idea of how (knowing that each updated version of my resources will have the exact same fields) I might be able to automate and track the movements with the least amount of manual work being required. Tracking just the change in 2 versions is not too bad, but wanting to have the perpetual records showing the movements over time.

Any general help is appreciated.

Regards
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

goesr

Well-known Member
Joined
Nov 15, 2013
Messages
672
Hi Excellling - (Love the name!!) - I have done a similar thing with data updates. What I did was to have a macro that copied the current worksheet and then renamed the worksheet with the current date i.e. 140107 for January 7, 2014 and placed the new sheet in the first position. That way I had a running history of updates, each with a current date. The code for this is fairly simple if you are interested.

Hope this helps.
 

Excellling

Board Regular
Joined
Sep 4, 2013
Messages
89
Hi Excellling - (Love the name!!) - I have done a similar thing with data updates. What I did was to have a macro that copied the current worksheet and then renamed the worksheet with the current date i.e. 140107 for January 7, 2014 and placed the new sheet in the first position. That way I had a running history of updates, each with a current date. The code for this is fairly simple if you are interested.

Hope this helps.

Hi Goesr,

Thanks for the reply.

Only question would be how big the file would get over time?

Other than that would love to see some code. Is it possible to always have the latest version linked to my forecasts after the macro is run?

Thanks
 

goesr

Well-known Member
Joined
Nov 15, 2013
Messages
672
Hi Excelling - Files can certainly get large over time, but in the past I have archived a copy and then removed older tabs that are no longer pertinent since they are kept in the archive file.

Hope this helps.
 

Forum statistics

Threads
1,137,298
Messages
5,680,678
Members
419,924
Latest member
Dhamodharan992

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