Hello, I'm looking for some help formulating a summary sheet of data located on several sheets within a work book. The work book contains 40 sheets all following the same format, with each sheet renamed as a specific employee.

I need a formula which would check each sheet, determine which employee belongs to which team, based on Supervisor's name entered (this is a drop down on the employee sheet), then avg. data found in a specific cell (it's the same on every sheet) and display it on the summary sheet, in the correct Team Avg's cell.

Example:

Sheet 1 renamed John Doe
Sheet 2 renamed John Smith
Sheet 3 renamed Paul Jones

John Doe's Supervisor entered as Bob
John Smith's Supervisor entered as Carol
Paul Jones's Supervisor entered as Carol

Avg calculated on each sheet for LD, Wireless, & Combined

So on: John Doe's Sheet it's shown as LD 75%, Wireless 80%, Combined 60%, on John Smith's LD 65%, Wireless 50%, Combined 45% & on Paul's LD 75%, Wireless 50% & Combined 65%.

I was hoping for a formula which would search the workbook, identify which employees worked for, in this case Carol, as indicated on their sheets. It would then give me an avg for just those employees on my Summary sheet in the listed catagories.

Summary Sheet:

Team 1 Bob: LD= 75%, Combined= 80%, Wireless= 60%
Team 2 Carol: LD= 70%, Combined= 50%, Wireless= 55%

I do apologize if this is rather confusing, & I really appreciate you taking a look at it.