# LOOKUP across multiple sheets

#### Alex O

##### Active Member
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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### braindiesel

##### Well-known Member
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

##### Well-known Member
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

##### Active Member
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

##### Well-known Member
you can share the worksheet by using something like

Replies
6
Views
227
Replies
0
Views
1K
Replies
17
Views
2K
Replies
3
Views
392
Replies
4
Views
702

1,191,360
Messages
5,986,196
Members
440,010
Latest member
cdotshel

### 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.

### Which adblocker are you using?

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

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