PDA

View Full Version : Combining Project Timesheet Help



charstoy
04-14-2009, 03:03 PM
Hi Folks,

I am helping a coworker with a task and would like advice on how to proceed. We use Excel 2002.

Her division (R&D) uses a standard timesheet that is completed and returned via email monthly (sometimes weekly). The worksheet lists all current projects, and the engineers add additional rows to their worksheets when they start a new project that is not already listed.

We are trying to automate the time collecting portion of my coworker's job, and want to integrate all of the emailed timesheets into a single worksheet. Currently she is entering the data manually.

I have tried to use the shared workbook/compare-and-merge function, but because many engineers can work on the same project, it isn't accumulating time but replacing it with each new merged sheet.

The employees are not interested in working with *anything* complicated (these folks are fairly inexperienced with excel). As such, the existing worksheet is basic with a pre-filled list of projects - all they need to do is add their time.

I have attached an example of the master that is currently used.

Really, I am just looking for a direction to move in; wondering whether this is outside the realm of Excel, and/or the parameters we are working with.

Thanks for your time,
Charlene

mdmackillop
04-14-2009, 03:38 PM
Hi Charlene
Welcome to VBAX
We can cetrainly do this in Excel, but need a bit more info. Can you submit a completed timesheet and also a Summary Sheet showing how the data is to be integrated. Are you simply copying the data and pasting in successive blocks, or do you want to add to existing values, or some other method.
Regards
MD

charstoy
04-14-2009, 08:24 PM
I have attached a master worksheet, and samples of 2 submissions (one includes a new project (smith)).

In practice, my coworker prints out the sheets and manually populates the cells (=1+2+4, etc.). If an engineer lists a new project, she then inserts a row and completes the necessary information.

Cues that are provided on the timesheets for name and total hours (if/then, conditional formatting, etc) are NOT vital to the success of this task, if that makes a difference. Meaning, we don't need them if they get in the way.

Please let me know what additional information I can provide, and thank you very much for this help.

Charlene

mdmackillop
04-15-2009, 12:55 AM
To make this work in the real world, you need to consider your new project nasmes carefully. These will need to be extracted and added to the master list if they do not appear there.
Potential issues:
Misspelling in successive submissions
Two+ sheets with different "versions" of the same Project
Anything else that can go wrong!

No time for a solution just now, but give these some thought.