LOOKUP across multiple sheets

[Alex O]

Hello All,
I am a viewer and major fan of MrExcel. This is my first post, so hopefully I can get some help with a major porblem I'm having!

The problem: I have a workbook that contains 13 worksheets,
1 RealTime_Time & Money
2 Employee1
3 Employee 2
4 " " " "
12 Employee 11
13 Client List

Each employee enters the date and time spent on a specific case. Said information is updated daily.

I need a formula or VBA that will look at the client number in "RealTime_Time & Money!C3" and then look at all 11 employee sheets and provide a detail of the total rproduction time spent on a particular case. Ideally, I would like to have a real time system of tracking the time and billing total on every case. Any help and or suggestions would be truly appreciated.

I can attach a copy of my work thus far if necessary....

 

[braindiesel]

Having the sheet attached would help, but I'll give this a shot.

First think I would do is name the range in each sheet
Select Employee1 sheet, shade your range and in the namebox left of the formula bar, type NUMBER1 or other such name
If you want to see on your summary page each individuals numbers
Then you use Vlookup.
If Cell A2 has the code you want sum'd then in B2 enter
=vlookup(a2,number1,200,false).
Replace the 200 with the column number you want the data pulled from, so if Employee 1's sheet is
A B C D
Code Start End TimeSpent
You would replace the 200 with 4 for the fourth column

Repeat that for each employee in cells C2, D2, etc with a =SUM(A2:L2) formula in the column M... something like that

Now if you don't want the individual numbers and only the total, I would write
=vlookup(a2,number1,200,false)+vlookup(a2,number2,200,false)+vlookup(a2,number3,200,false)...=vlookup(a2,number12,200,false)

Something like that.

Does that help?

 

[jbeaucaire]

Something like this where it looks across all the sheets in one column for a matching value (C3?) and brings back the values from another cell in the same row...across all sheets.

=SUM(SUMIF(INDIRECT("Employee"&{1,2,3,4,5,6,7,8,9,10,11}&"!A1:A1000"),"="&C3,INDIRECT("Employee"&{1,2,3,4,5,6,7,8,9,10,11}&"!B1:B1000")))

 

[Alex O]

Your time and feedback is appreciated, however, I feel I may be lacking in my explanation of what I'm trying to accomplish. jbeaucaire's solution is close, but I'm having trouble getting it to recognize the various spreadsheets (I'm getting #Ref error). I've included my email address, and am willing to send the spreadsheet which may simplify the task of explaining what it is I'm trying to accomplish. *Is it possible to attach xls files directly to the query? Any help is appreciated...I'm on a hard deadline and sinking fast!
aoconnel@lstokesphd.com

 

[texasalynn]

you can share the worksheet by using something like
To share your file, upload it to box.net