LOOKUP across multiple sheets

Alex O

Active Member
Joined
Mar 16, 2009
Messages
345
Office Version
  1. 365
Platform
  1. Windows
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....
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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?
 
Upvote 0
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")))
 
Upvote 0
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
 
Upvote 0
you can share the worksheet by using something like
To share your file, upload it to box.net
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top