Trying to build a master spreadsheet to pull hours from multiple time sheets

Hi Excel experts!

I am a long time excel user but I have come across a road block so I need some advice.

I am creating a master spreadsheet to pull the numbers of hours worked on multiple time sheets. The master is set up with a list of job numbers in column A and then Employee names across the top. There are 26 time sheet files per employee per year. Each time sheet has the job numbers and the number of hours spent working on each job.

The purpose of the master is to calculate the number of hours spent on each job.

I have attempted to use lookup BUT the job numbers on the time sheets are not listed in numerical order so it doesn't work.

Time sheet columns are set up as follows:
Job Number / Description / hours worked

Any ideas on how to make this work?

I appreciate your help!!

Roxanne

Hi, here are two examples of the files I am using. Thanks so much for your help!!

Hi,
For some reason my computer is not reading the file, when I open the file I am getting this error:

Removed Records: Formula from /xl/worksheets/sheet1.xml part
Removed Records: Formula from /xl/calcChain.xml part (Calculation properties)

I am trying to upload a file with my screen shots on it so you can see the error. i did unzip bit it seems to be taking the formulas out of the file. I just emailed you the word file with the screen shot

OK I will. Is it possible for you to type the formula you used into the forum reply just so I can work with it without having to open that file?

Hello! I got everything sorted out. Now I see how to do the formula, thank you! I am coming across a problem though.

When the time sheet files are open on my computer it works! but when I close the files, the formula results in a #VALUE.

Any idea why?

When the the source file is open the formula just contains the file name. When I close the source file, the destination fil automatically changes the file path to include everything P:\folder\folder\[filename.xls]

I would assume this is what is supposed to happen, correct?

You should be able to use SUMPRODUCT.

I am trying SUMPRODUCT but it doens't seem to be giving me the right result. It is giving me a 0 when it should give me 2. This is the formula i'm using

=SUMPRODUCT('P:\employee\2012\[employee1.xls]Sheet1'!$A$3:$A$34=$A10000,'P:\employee\2012\[1.xls]Sheet1'!$J$3:$J$34)