# VLOOKUP across multiple sheets

#### Met Fan

##### Board Regular
I have a spreadsheet that records payroll. Within the file there are 52 worksheets (one per week for the entire year).

Every worksheet is formatted identically. I would like to add a 53rd tab that totals all of the entries in the weekly sheets. I want to use VLOOKUP because there is no guarantee that new employees will not be added or existing ones removed during the year.

Is there anyway to do a global VLOOKUP throughout the sheet or do I have to add a formula that adds each individually such as:
=VLOOKUP(B5,'01.06.09'!B3:K40,6,FALSE)+=VLOOKUP(B5,'01.13.09'!B3:K40,6,FALSE)+ ...

THANKS

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### Met Fan

##### Board Regular
Thank you for the input. I've entered the following formula:

=sumproduct(sumif(indirect("'"&SheetList&"'!B3:B29),B3,Indirect(""&SheetList&'''!G3:G29")))

I've created a list of the tab names and called the range SheetList.
B3:B29 contains the list of employee names
B3 contains the name of the employee I am adding the time up for.
G3:G29 contains the range withing the various weeks where the hours are to be added.

When I hit enter I am told my function contains too few arguments. When I hit OK the cursor goes to a point between the first and second Parenthese at the end of the formula.

Can you tell what I am doing incorrectly?

##### MrExcel MVP
Thank you for the input. I've entered the following formula:

=sumproduct(sumif(indirect("'"&SheetList&"'!B3:B29),B3,Indirect(""&SheetList&'''!G3:G29")))

I've created a list of the tab names and called the range SheetList.
B3:B29 contains the list of employee names
B3 contains the name of the employee I am adding the time up for.
G3:G29 contains the range withing the various weeks where the hours are to be added.

When I hit enter I am told my function contains too few arguments. When I hit OK the cursor goes to a point between the first and second Parenthese at the end of the formula.

Can you tell what I am doing incorrectly?

I think it's just missing some quotes...

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!B3:B29"),B3,INDIRECT("'"&SheetList&'''!G3:G29")))

#### Met Fan

##### Board Regular
=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!b3:b9"),B3,INDIRECT("'"&SheetList&"'!G3:G29")))

I tried the above formula and it returns #REF!

#### Met Fan

##### Board Regular
I revised it to:
=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!b3:b9"),B3,INDIRECT("'"&SheetList&"'!G3:G29")))

and it returns #REF!

##### MrExcel MVP
I revised it to:
=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!b3:b9"),B3,INDIRECT("'"&SheetList&"'!G3:G29")))

and it returns #REF!

The ranges in B and in G must be equally sized...

#### Met Fan

##### Board Regular
I appreciate you assistance to date. I've changed to formula to:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!b3:b29"),B3,INDIRECT("'"&SheetList&"'!G3:G29")))

I still get #REF! When I enter the formula the range b3:b29 does not change to caps. Can this be where the problem lies?

Replies
3
Views
291
Replies
1
Views
136
Replies
9
Views
350
Replies
6
Views
586
Replies
3
Views
165

1,191,095
Messages
5,984,637
Members
439,898
Latest member
Zack0611

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