Searching Multiple Timesheets for a project code and then....

[Saitei]

I need some help on this one!!

I am trying to scan multiple timesheets in excel for a project code and then
capturing the hours worked for that project. I then need to have the hours
worked summed on a different work sheet to track hours worked for multiple
disciplines on one engineering project. (See below for example)

Example:
Timesheet 1:
Monday Tueday Wednesday
Thursday Friday
Project 'A' 4 2 8
0.5 0
Project 'B' 1 0 0
6.5 2.5
Project 'C' 3 6 0
1 5.5

Timesheet 2:
Monday Tueday Wednesday
Thursday Friday
Project 'A' 6 1 2
0.5 0
Project 'B' 1 1 0
6.5 2.5
Project 'C' 1 6 6
1 5.5


Tracking Sheet 3 :
(Total Hours worked by Project from timesheet 1 & 2)

Project 'A' 24
Project 'B' 21
Project 'C' 35

Any help on this would be greatly appreciated. Thanks in advance!!! 8^)

 

[Matt's Dad]

If timesheets are on separate worksheets the easiest way to do it, provided
there aren't that many timecards, is to add a Total column to each timesheet
(Reference column G in formula) and then use sumif:

=SUMIF(Sheet1!$A:$A,Sheet3!$A3,Sheet1!$G:$G)+SUMIF(Sheet2!$A:$A,Sheet3!$A3,Sheet2!$G:$G)

Just keep adding on to the formula for each timecard. Not particularly
high-tech but it's a reliable way to get the job done.

You can read up on Sumif and Sumproduct here:
http://www.journalofaccountancy.com/Issues/2009/Jul/20091493