Hlookup (sum)

[Shane]

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

 

[Shane]

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.

 

[Max]

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