VLOOKUP across multiple sheets

[Met Fan]

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

 

[Aladin Akyurek]

See

http://www.mrexcel.com/forum/showthread.php?t=119020

for some options.

 

[Met Fan]

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?

 

[Aladin Akyurek]

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]

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

I tried the above formula and it returns #REF!

 

[Met Fan]

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

and it returns #REF!

 

[Aladin Akyurek]

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]

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?