This solution depends on two things being the same on all of the individual
sheets you now have:
the cell that the person's name is in, and
the cell that holds their total hours worked.

If the total hours worked is not the same on all of the sheets, you need to
go through them and set them up so that one cell on each sheet holds a copy
of that total. After that, it's a piece of cake.

Begin by inserting a new sheet into your workbook. It can be anywhere in
the workbook; beginning, end, somewhere in the middle of the mess.

Next, with the workbook open, press [Alt]+[F11] to open the Visual Basic
Editor and when it opens, choose Insert -- Module to start a new code
module. Copy the code below and paste it into that module. Change the 2
'Const' values to hold the addresses of the cells that hold the Name and
Total Hours on all of the other sheets. Close the VB Editor.

Save the workbook with a new name, just in case something goes wrong. That
way you'll still have your original book with all its data in one piece to
start over with.

With the NEW SHEET you inserted selected, and without having sheets grouped,
use Tools -- Macro -- Macros to run the macro you just put into the book.
When it is finished, you should have a list of names with their total hours,
sorted by total hours worked and then by name in the case of a tie for hours.
This method also permits you to identify such tied for most hours situations.

Sub ListHoursWorked()
'change these two Const values to hold the appropriate
'cell addresses - should be the same for all sheets
'in your workbook
Const nameCell = "A1" ' cell with person's name in it
Const totalHrsCell = "B1" ' cell with TOTAL hours in it

Dim sortRange As Range
Dim sKey1 As Range
Dim sKey2 As Range
Dim anyWS As Worksheet

Application.ScreenUpdating = False
ActiveSheet.Cells.ClearContents
ActiveSheet.Range("A1") = "NAME"
ActiveSheet.Range("B1") = "HRS"
For Each anyWS In ThisWorkbook.Worksheets
If anyWS.Name < ActiveSheet.Name Then
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = _
anyWS.Range(nameCell)
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 1) = _
anyWS.Range(totalHrsCell)
End If
Next
Set sKey1 = ActiveSheet.Range("B2")
Set sKey2 = ActiveSheet.Range("A2")
Set sortRange = ActiveSheet.Range("A1:" & _
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Address)
sortRange.Sort Key1:=sKey1, Order1:=xlDescending, Key2:=sKey2, _
Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
ActiveSheet.Range("A1").Select

End Sub


"aglen" wrote:

I am trying to return the name of a person who worked the most hours in my
volunteer table. The columns include the person's name, date worked, number
of hours per shift and total hours worked. There are 100+ people that each
have their own worksheet. I would like to use a lookup to return the name of
the person with the most hours. How would I do this? I tried grouping the
sheets and referencing the ranges I want to use.

Thanks.