Summing VLOOKUPs Across Multiple Sheets

[bachya]

Hi all - have a tricky situation: I was handed 20 separate worksheets, each with a list of all the employees in our company and their yearly numbers (e.g., for the sheet labeled 2007, it's everyone's 2007 numbers). These sheets go from 2007 to 1987.

I'm trying to consolidate all these sheets into one (so I can properly use all of Excel's functionality). I want to have a summary sheet that totals all the categories for all employees in the last 20 years.

For example: I'd like to have a column that says "Total Sales" in the summary sheet - in this cell, I'd like to have a summation of that employee's sales numbers from 1987 to 2007. Rinse and repeat for every column in the individual sheets.

I was thinking of doing VLOOKUPs for the relevant info across the 20 sheets, but I don't really know how to add them together. Does anyone have an idea on how this could be done? I hope it makes sense.

Thanks!

[sglife]

I was thinking of doing VLOOKUPs for the relevant info across the 20 sheets, but I don't really know how to add them together

Sum(vlookup1,vlookup2,…..vlookup20)

[bachya]

Haha, I was afraid you'd say that. This is why you keep data in one sheet!!

Thanks for your help.

[snasui]

Try this.

1. List sheet's names in any area and define name, say "AllSh".
2. Enter the formula on summary sheet as below.

Please Login or Register  to view this content.

Note:
1. A2:A4 contain employee's number in each sheet.
2. B2:B4 contain employee's total sales in each sheet.
3. "AllSh" is name of all sheets.
4. A2 is employee's number on sheet summary.