Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hlookup (sum)
I have a yearly roster with 4 staff 1st sheet="roster". Am trying to sum
hours for each week for staff on their own sheet, eg 2nd sheet="kerry". Am assigning each week with name eg week1 to week52 range so next year only have to change name range and leave individual staff sheet alone.Eg below says kerry=13 hours leave in week 1, sue=14hours week1, john=10hrs, brad=10hrs. Need to label range at least b2 (m) to h6. each staff sheet then calls their own row from the named range (week1) and sums the hours for their row.??? A B C D E F G H 1 1 2 3 4 5 6 7 2 m t w t f s s 3 kerry 8 3 2 4 sue 1 6 7 5 john 1 9 6 brad 3 7 -- ShaneBrowning, many thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hlookup (sum)
ie. lookup range "lw1" (leave for week 1 which is range a1:h6) and sum row 3
for kerry or row 4 for sue etc. next year just need to relabel range "lw1" to cater for day/date difference. -- ShaneBrowning "Shane" wrote: I have a yearly roster with 4 staff 1st sheet="roster". Am trying to sum hours for each week for staff on their own sheet, eg 2nd sheet="kerry". Am assigning each week with name eg week1 to week52 range so next year only have to change name range and leave individual staff sheet alone.Eg below says kerry=13 hours leave in week 1, sue=14hours week1, john=10hrs, brad=10hrs. Need to label range at least b2 (m) to h6. each staff sheet then calls their own row from the named range (week1) and sums the hours for their row.??? A B C D E F G H 1 1 2 3 4 5 6 7 2 m t w t f s s 3 kerry 8 3 2 4 sue 1 6 7 5 john 1 9 6 brad 3 7 -- ShaneBrowning, many thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hlookup (sum)
One way to set it up to work for the whole year (52 weeks)
illustrated in this sample: http://www.freefilehosting.net/download/3bmle 52 weeks template by staff.xls Source data is assumed in Roster, within cols A to H, with 6 lines per week Click Insert Name Define Put under "Names in workbook:": WSN Put in the "Refers to:" box: =MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1") ))+1,32) Click OK The above defines WSN as a name we can use to refer to the sheetname in formulas. It will auto-extract the sheetname implicitly. Technique came from a post by Harlan. In a new sheet named: Kerry In A1: =WSN (this is just a label to display the sheetname, ie name of staff, prominently for reference) Input in B1: Roster (the source sheetname) List the 52 weeks in A2 down, viz.: Wk1, Wk2, Wk3,... Wk52 Then place in B2: =IF(ISNA(MATCH($A2,INDIRECT("'"&B$1&"'!A:A"),0))," ",SUM(OFFSET(INDIRECT("'"&B$1&"'!B1:H1"),MATCH(WSN ,OFFSET(INDIRECT("'"&B$1&"'!A"&MATCH($A2,INDIRECT( "'"&B$1&"'!A:A"),0)),,,6),0)+MATCH($A2,INDIRECT("' "&B$1&"'!A:A"),0)-2,))) Copy B2 down to B53. Col B will return the required totals for each of the 52 weeks for Kerry. To easily propagate sheets for other staff, just make copies of this sheet and rename the sheets accordingly as: Sue, Brad, John -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Shane" wrote: ie. lookup range "lw1" (leave for week 1 which is range a1:h6) and sum row 3 for kerry or row 4 for sue etc. next year just need to relabel range "lw1" to cater for day/date difference. I have a yearly roster with 4 staff 1st sheet="roster". Am trying to sum hours for each week for staff on their own sheet, eg 2nd sheet="kerry". Am assigning each week with name eg week1 to week52 range so next year only have to change name range and leave individual staff sheet alone.Eg below says kerry=13 hours leave in week 1, sue=14hours week1, john=10hrs, brad=10hrs. Need to label range at least b2 (m) to h6. each staff sheet then calls their own row from the named range (week1) and sums the hours for their row.??? A B C D E F G H 1 1 2 3 4 5 6 7 2 m t w t f s s 3 kerry 8 3 2 4 sue 1 6 7 5 john 1 9 6 brad 3 7 -- ShaneBrowning, many thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HLOOKUP | Excel Discussion (Misc queries) | |||
hlookup | Excel Discussion (Misc queries) | |||
Hlookup? | Excel Worksheet Functions | |||
HLOOKUP | Excel Worksheet Functions | |||
HLookup ??? | Excel Discussion (Misc queries) |