Using excel to track project movements

[Excellling]

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

 

[XLfriend777]

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]

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

 

[XLfriend777]

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.