Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
return value from range of worksheets
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
return value from range of worksheets
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 he 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 "aglen" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
return value from range of worksheets
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return date if in range, else return blank | Excel Worksheet Functions | |||
compare two worksheets and return a corresponding column | Excel Discussion (Misc queries) | |||
Compare 2 Worksheets and return differences in a third. | Excel Worksheet Functions | |||
matching two worksheets and return to different value | Excel Worksheet Functions | |||
Function to Return another Worksheets Name | Excel Worksheet Functions |