Complex "VLOOKUP" type of formula over multiple sheets

**tsparacino** · 04-04-2011, 10:26 AM

Ok, I've reserached and researched and I'm now blue in the face.

I have a workbook with on average 10-20 worksheets. The workbook is our time sheets and the worksheets are the different job sites. I'm trying to create a formula that calculates each employees hours in total (across all worksheets). Calculating it for each sheet of course is easy, but I need to total the hours for each employee over ALL the worksheets.

Example: Tony works at sites 1,3 and 4 which are noted on worksheets 1,3 and 4. The vlookup seems to only return the first find instead of all the finds. The second challenge is that the site is what is static, not the worker, so every week the employees that work at each site changes. the vlookup function, from what I've expiramented with, doesn't like the table array to change once defined. I know this is a pretty complex task but any help would be HUGELY appreciated. You'd make me the hero of the day!

If I need to attach a copy of a sample I can do so...

**NBVC** · 04-04-2011, 10:57 AM

Have a look here to see if this method can be used for your project:

Sum over Multiple sheets Based on Criteria

**tsparacino** · 04-04-2011, 02:37 PM

Holy mother of all things wonderful and awesome, IT WORKED!! The heavens have parted and made way for a glorious day!! I was making error upon error week after week because I was miscalculating hours by hand. Thanks a million for your help!