return value from range of worksheets

[aglen]

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.

 

[Bernie Deitrick]

As much as Excel looks like a 3D spreadsheet, its actual 3D functionality is
very limited, especially if you are not comfortable with VBA solutions.
Excel is really good at working with data tables, though, and there is
really no reason why you could not do exactly what you want with just one
worksheet, and then using data filters or pivot tables to get the data of
interest. You might be able to consolidate 100 ranges into a pivot table,
but I have never tried it with more than 2 sheets...

If you were to take all the sheets and copy your data into one, it would be
an incredibly simple task. IF you would like to try to merge all your data
to use a pivot table, take a look here:

http://www.rondebruin.nl/copy2.htm


As it is, you might be able to do it if you want to use a
User-Defined-Function and if your individual sheets are structure
identically. If so, then post back.

HTH,
Bernie
MS Excel MVP

 

[JLatham]

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