mingandmong
Active Member
- Joined
- Oct 15, 2014
- Messages
- 339
Hi
im using excel 2010 and have a workbook with a worksheet YTD with 20,000 rows
Im after some advice or solutions so that i can have a summery sheet that when i enter the employee associate number(always in column A)
It will return if possible the year,week No and days or dates that person has booked off, the weekly headers are all standerd for the entries, but the employee entry varies from the header
ie
11111 Jo bloggs in A7 is the first entry (from the header)
11111 Jo bloggs in A19 is the second entry
11111 Jo bloggs in A9620 is the third entry
below is just a snapshot of the 20,000 rows, your help and guidance is appreciated...Dean
im using excel 2010 and have a workbook with a worksheet YTD with 20,000 rows
Im after some advice or solutions so that i can have a summery sheet that when i enter the employee associate number(always in column A)
It will return if possible the year,week No and days or dates that person has booked off, the weekly headers are all standerd for the entries, but the employee entry varies from the header
ie
11111 Jo bloggs in A7 is the first entry (from the header)
11111 Jo bloggs in A19 is the second entry
11111 Jo bloggs in A9620 is the third entry
below is just a snapshot of the 20,000 rows, your help and guidance is appreciated...Dean
Code:
[B]YTD Holidays[/B]
[TABLE]
<colgroup><col style="font-weight:bold; width:30px; "><col style="width:103px;"><col style="width:226px;"><col style="width:158px;"><col style="width:48px;"><col style="width:48px;"><col style="width:48px;"><col style="width:48px;"><col style="width:48px;"><col style="width:48px;"><col style="width:48px;"></colgroup><tbody>[TR="bgcolor: #cacaca"]
[TD]*[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: left"]YTD calendar[/TD]
[TD="align: center"]Next Week[/TD]
[TD]Total Employee's Off [/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]WEEK[/TD]
[TD="align: center"]2014[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #c0c0c0, align: center"]32[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #ffff00, align: center"]DAYS *[/TD]
[TD="bgcolor: #ffff00, align: center"]Employee[/TD]
[TD="bgcolor: #ffff00"]*[/TD]
[TD="bgcolor: #ffff00, align: center"]Sun[/TD]
[TD="bgcolor: #ffff00, align: center"]Mon[/TD]
[TD="bgcolor: #ffff00, align: center"]Tue[/TD]
[TD="bgcolor: #ffff00, align: center"]Wed[/TD]
[TD="bgcolor: #ffff00, align: center"]Thu[/TD]
[TD="bgcolor: #ffff00, align: center"]Fri[/TD]
[TD="bgcolor: #ffff00, align: center"]Sat[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="bgcolor: #ffff00, align: center"]ASSOCIATE No[/TD]
[TD="bgcolor: #ffff00, align: center"]NAME[/TD]
[TD="bgcolor: #ffff00"]*[/TD]
[TD="bgcolor: #ffff00, align: center"]03-Aug[/TD]
[TD="bgcolor: #ffff00, align: center"]04-Aug[/TD]
[TD="bgcolor: #ffff00, align: center"]05-Aug[/TD]
[TD="bgcolor: #ffff00, align: center"]06-Aug[/TD]
[TD="bgcolor: #ffff00, align: center"]07-Aug[/TD]
[TD="bgcolor: #ffff00, align: center"]08-Aug[/TD]
[TD="bgcolor: #ffff00, align: center"]09-Aug[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #ff0000, colspan: 3, align: center"]PRINTING[/TD]
[TD="bgcolor: #c0c0c0, align: center"]1[/TD]
[TD="bgcolor: #c0c0c0, align: center"]1[/TD]
[TD="bgcolor: #c0c0c0, align: center"]2[/TD]
[TD="bgcolor: #c0c0c0, align: center"]0[/TD]
[TD="bgcolor: #c0c0c0, align: center"]1[/TD]
[TD="bgcolor: #c0c0c0, align: center"]1[/TD]
[TD="bgcolor: #c0c0c0, align: center"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: left"]11111[/TD]
[TD="align: left"]JOE BLOGGS[/TD]
[TD]#N/A[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: left"]99999[/TD]
[TD="align: left"]SYNDEY SMITH[/TD]
[TD]#N/A[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: left"]YTD calendar[/TD]
[TD="align: center"]Next Week[/TD]
[TD]Total Employee's Off [/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: center"]WEEK[/TD]
[TD="align: center"]2014[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="bgcolor: #c0c0c0, align: center"]33[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="bgcolor: #ffff00, align: center"]DAYS *[/TD]
[TD="bgcolor: #ffff00, align: center"]Employee[/TD]
[TD="bgcolor: #ffff00"]*[/TD]
[TD="bgcolor: #ffff00, align: center"]Sun[/TD]
[TD="bgcolor: #ffff00, align: center"]Mon[/TD]
[TD="bgcolor: #ffff00, align: center"]Tue[/TD]
[TD="bgcolor: #ffff00, align: center"]Wed[/TD]
[TD="bgcolor: #ffff00, align: center"]Thu[/TD]
[TD="bgcolor: #ffff00, align: center"]Fri[/TD]
[TD="bgcolor: #ffff00, align: center"]Sat[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="bgcolor: #ffff00, align: center"]ASSOCIATE No[/TD]
[TD="bgcolor: #ffff00, align: center"]NAME[/TD]
[TD="bgcolor: #ffff00"]*[/TD]
[TD="bgcolor: #ffff00, align: center"]10-Aug[/TD]
[TD="bgcolor: #ffff00, align: center"]11-Aug[/TD]
[TD="bgcolor: #ffff00, align: center"]12-Aug[/TD]
[TD="bgcolor: #ffff00, align: center"]13-Aug[/TD]
[TD="bgcolor: #ffff00, align: center"]14-Aug[/TD]
[TD="bgcolor: #ffff00, align: center"]15-Aug[/TD]
[TD="bgcolor: #ffff00, align: center"]16-Aug[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="bgcolor: #ff0000, colspan: 3, align: center"]PRINTING[/TD]
[TD="bgcolor: #c0c0c0, align: center"]2[/TD]
[TD="bgcolor: #c0c0c0, align: center"]0[/TD]
[TD="bgcolor: #c0c0c0, align: center"]1[/TD]
[TD="bgcolor: #c0c0c0, align: center"]1[/TD]
[TD="bgcolor: #c0c0c0, align: center"]0[/TD]
[TD="bgcolor: #c0c0c0, align: center"]1[/TD]
[TD="bgcolor: #c0c0c0, align: center"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: left"]88888[/TD]
[TD="align: left"]PAUL YOUG[/TD]
[TD]#N/A[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: left"]11111[/TD]
[TD="align: left"]JOE BLOGGS[/TD]
[TD]#N/A[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]22[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]23[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]9612[/TD]
[TD="align: left"]YTD calendar[/TD]
[TD="align: center"]Next Week[/TD]
[TD]Total Employee's Off [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]9613[/TD]
[TD="align: center"]WEEK[/TD]
[TD="align: center"]2015[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]9614[/TD]
[TD="bgcolor: #c0c0c0, align: center"]13[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]9615[/TD]
[TD="bgcolor: #ffff00, align: center"]DAYS *[/TD]
[TD="bgcolor: #ffff00, align: center"]Employee[/TD]
[TD="bgcolor: #ffff00"]*[/TD]
[TD="bgcolor: #ffff00, align: center"]Sun[/TD]
[TD="bgcolor: #ffff00, align: center"]Mon[/TD]
[TD="bgcolor: #ffff00, align: center"]Tue[/TD]
[TD="bgcolor: #ffff00, align: center"]Wed[/TD]
[TD="bgcolor: #ffff00, align: center"]Thu[/TD]
[TD="bgcolor: #ffff00, align: center"]Fri[/TD]
[TD="bgcolor: #ffff00, align: center"]Sat[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]9616[/TD]
[TD="bgcolor: #ffff00, align: center"]ASSOCIATE No[/TD]
[TD="bgcolor: #ffff00, align: center"]NAME[/TD]
[TD="bgcolor: #ffff00"]*[/TD]
[TD="bgcolor: #ffff00, align: center"]22-Mar[/TD]
[TD="bgcolor: #ffff00, align: center"]23-Mar[/TD]
[TD="bgcolor: #ffff00, align: center"]24-Mar[/TD]
[TD="bgcolor: #ffff00, align: center"]25-Mar[/TD]
[TD="bgcolor: #ffff00, align: center"]26-Mar[/TD]
[TD="bgcolor: #ffff00, align: center"]27-Mar[/TD]
[TD="bgcolor: #ffff00, align: center"]28-Mar[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]9617[/TD]
[TD="bgcolor: #ff0000, colspan: 3, align: center"]PRINTING[/TD]
[TD="bgcolor: #c0c0c0, align: center"]0[/TD]
[TD="bgcolor: #c0c0c0, align: center"]1[/TD]
[TD="bgcolor: #c0c0c0, align: center"]1[/TD]
[TD="bgcolor: #c0c0c0, align: center"]0[/TD]
[TD="bgcolor: #c0c0c0, align: center"]1[/TD]
[TD="bgcolor: #c0c0c0, align: center"]1[/TD]
[TD="bgcolor: #c0c0c0, align: center"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]9618[/TD]
[TD="align: left"]55555[/TD]
[TD="align: left"]PETER JAMES[/TD]
[TD]#N/A[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]9619[/TD]
[TD="align: left"]77777[/TD]
[TD="align: left"]ALICE TYRYIL[/TD]
[TD]#N/A[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]9620[/TD]
[TD="align: left"]11111[/TD]
[TD="align: left"]JOE BLOGGS[/TD]
[TD]#N/A[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]9621[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]9622[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]9623[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]9624[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE]
<tbody>[TR]
[TD][B]Spreadsheet Formulas[/B][/TD]
[/TR]
[TR]
[TD][TABLE]
<tbody>[TR="bgcolor: #cacaca"]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]D1[/TD]
[TD]=COUNTIF(D7:D11,"H")[/TD]
[/TR]
[TR]
[TD]E1[/TD]
[TD]=COUNTIF(E7:E11,"H")[/TD]
[/TR]
[TR]
[TD]F1[/TD]
[TD]=COUNTIF(F7:F11,"H")[/TD]
[/TR]
[TR]
[TD]G1[/TD]
[TD]=COUNTIF(G7:G11,"H")[/TD]
[/TR]
[TR]
[TD]H1[/TD]
[TD]=COUNTIF(H7:H11,"H")[/TD]
[/TR]
[TR]
[TD]I1[/TD]
[TD]=COUNTIF(I7:I11,"H")[/TD]
[/TR]
[TR]
[TD]J1[/TD]
[TD]=COUNTIF(J7:J11,"H")[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]=WEEKNUM(J5,2)[/TD]
[/TR]
[TR]
[TD]E5[/TD]
[TD]=+D5+1[/TD]
[/TR]
[TR]
[TD]F5[/TD]
[TD]=+E5+1[/TD]
[/TR]
[TR]
[TD]G5[/TD]
[TD]=+F5+1[/TD]
[/TR]
[TR]
[TD]H5[/TD]
[TD]=+G5+1[/TD]
[/TR]
[TR]
[TD]I5[/TD]
[TD]=+H5+1[/TD]
[/TR]
[TR]
[TD]J5[/TD]
[TD]=+I5+1[/TD]
[/TR]
[TR]
[TD]D6[/TD]
[TD]=COUNTIF(D7:D11,"H")[/TD]
[/TR]
[TR]
[TD]E6[/TD]
[TD]=COUNTIF(E7:E11,"H")[/TD]
[/TR]
[TR]
[TD]F6[/TD]
[TD]=COUNTIF(F7:F11,"H")[/TD]
[/TR]
[TR]
[TD]G6[/TD]
[TD]=COUNTIF(G7:G11,"H")[/TD]
[/TR]
[TR]
[TD]H6[/TD]
[TD]=COUNTIF(H7:H11,"H")[/TD]
[/TR]
[TR]
[TD]I6[/TD]
[TD]=COUNTIF(I7:I11,"H")[/TD]
[/TR]
[TR]
[TD]J6[/TD]
[TD]=COUNTIF(J7:J11,"H")[/TD]
[/TR]
[TR]
[TD]C7[/TD]
[TD]=IF(A7="","",VLOOKUP[color=#008000](A7,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C8[/TD]
[TD]=IF(A8="","",VLOOKUP[color=#008000](A8,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]B9[/TD]
[TD]=IF(A9="","",VLOOKUP[color=#008000](A9,employees,2,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C9[/TD]
[TD]=IF(A9="","",VLOOKUP[color=#008000](A9,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]B10[/TD]
[TD]=IF(A10="","",VLOOKUP[color=#008000](A10,employees,2,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C10[/TD]
[TD]=IF(A10="","",VLOOKUP[color=#008000](A10,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]B11[/TD]
[TD]=IF(A11="","",VLOOKUP[color=#008000](A11,employees,2,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C11[/TD]
[TD]=IF(A11="","",VLOOKUP[color=#008000](A11,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]D12[/TD]
[TD]=COUNTIF(D18:D74,"H")[/TD]
[/TR]
[TR]
[TD]E12[/TD]
[TD]=COUNTIF(E18:E74,"H")[/TD]
[/TR]
[TR]
[TD]F12[/TD]
[TD]=COUNTIF(F18:F74,"H")[/TD]
[/TR]
[TR]
[TD]G12[/TD]
[TD]=COUNTIF(G18:G74,"H")[/TD]
[/TR]
[TR]
[TD]H12[/TD]
[TD]=COUNTIF(H18:H74,"H")[/TD]
[/TR]
[TR]
[TD]I12[/TD]
[TD]=COUNTIF(I18:I74,"H")[/TD]
[/TR]
[TR]
[TD]J12[/TD]
[TD]=COUNTIF(J18:J74,"H")[/TD]
[/TR]
[TR]
[TD]A14[/TD]
[TD]=WEEKNUM(J16,2)[/TD]
[/TR]
[TR]
[TD]D16[/TD]
[TD]=J5+1[/TD]
[/TR]
[TR]
[TD]E16[/TD]
[TD]=+D16+1[/TD]
[/TR]
[TR]
[TD]F16[/TD]
[TD]=+E16+1[/TD]
[/TR]
[TR]
[TD]G16[/TD]
[TD]=+F16+1[/TD]
[/TR]
[TR]
[TD]H16[/TD]
[TD]=+G16+1[/TD]
[/TR]
[TR]
[TD]I16[/TD]
[TD]=+H16+1[/TD]
[/TR]
[TR]
[TD]J16[/TD]
[TD]=+I16+1[/TD]
[/TR]
[TR]
[TD]D17[/TD]
[TD]=COUNTIF(D18:D38,"H")[/TD]
[/TR]
[TR]
[TD]E17[/TD]
[TD]=COUNTIF(E18:E38,"H")[/TD]
[/TR]
[TR]
[TD]F17[/TD]
[TD]=COUNTIF(F18:F38,"H")[/TD]
[/TR]
[TR]
[TD]G17[/TD]
[TD]=COUNTIF(G18:G38,"H")[/TD]
[/TR]
[TR]
[TD]H17[/TD]
[TD]=COUNTIF(H18:H38,"H")[/TD]
[/TR]
[TR]
[TD]I17[/TD]
[TD]=COUNTIF(I18:I38,"H")[/TD]
[/TR]
[TR]
[TD]J17[/TD]
[TD]=COUNTIF(J18:J38,"H")[/TD]
[/TR]
[TR]
[TD]C18[/TD]
[TD]=IF(A18="","",VLOOKUP[color=#008000](A18,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C19[/TD]
[TD]=IF(A19="","",VLOOKUP[color=#008000](A19,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]B20[/TD]
[TD]=IF(A20="","",VLOOKUP[color=#008000](A20,employees,2,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C20[/TD]
[TD]=IF(A20="","",VLOOKUP[color=#008000](A20,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]B21[/TD]
[TD]=IF(A21="","",VLOOKUP[color=#008000](A21,employees,2,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C21[/TD]
[TD]=IF(A21="","",VLOOKUP[color=#008000](A21,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]B22[/TD]
[TD]=IF(A22="","",VLOOKUP[color=#008000](A22,employees,2,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C22[/TD]
[TD]=IF(A22="","",VLOOKUP[color=#008000](A22,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]B23[/TD]
[TD]=IF(A23="","",VLOOKUP[color=#008000](A23,employees,2,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C23[/TD]
[TD]=IF(A23="","",VLOOKUP[color=#008000](A23,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]D9612[/TD]
[TD]=COUNTIF(D9618:D9674,"H")[/TD]
[/TR]
[TR]
[TD]E9612[/TD]
[TD]=COUNTIF(E9618:E9674,"H")[/TD]
[/TR]
[TR]
[TD]F9612[/TD]
[TD]=COUNTIF(F9618:F9674,"H")[/TD]
[/TR]
[TR]
[TD]G9612[/TD]
[TD]=COUNTIF(G9618:G9674,"H")[/TD]
[/TR]
[TR]
[TD]H9612[/TD]
[TD]=COUNTIF(H9618:H9674,"H")[/TD]
[/TR]
[TR]
[TD]I9612[/TD]
[TD]=COUNTIF(I9618:I9674,"H")[/TD]
[/TR]
[TR]
[TD]J9612[/TD]
[TD]=COUNTIF(J9618:J9674,"H")[/TD]
[/TR]
[TR]
[TD]B9613[/TD]
[TD]=B9513[/TD]
[/TR]
[TR]
[TD]A9614[/TD]
[TD]=WEEKNUM(J9616,2)[/TD]
[/TR]
[TR]
[TD]D9616[/TD]
[TD]=J9516+1[/TD]
[/TR]
[TR]
[TD]E9616[/TD]
[TD]=+D9616+1[/TD]
[/TR]
[TR]
[TD]F9616[/TD]
[TD]=+E9616+1[/TD]
[/TR]
[TR]
[TD]G9616[/TD]
[TD]=+F9616+1[/TD]
[/TR]
[TR]
[TD]H9616[/TD]
[TD]=+G9616+1[/TD]
[/TR]
[TR]
[TD]I9616[/TD]
[TD]=+H9616+1[/TD]
[/TR]
[TR]
[TD]J9616[/TD]
[TD]=+I9616+1[/TD]
[/TR]
[TR]
[TD]D9617[/TD]
[TD]=COUNTIF(D9618:D9638,"H")[/TD]
[/TR]
[TR]
[TD]E9617[/TD]
[TD]=COUNTIF(E9618:E9638,"H")[/TD]
[/TR]
[TR]
[TD]F9617[/TD]
[TD]=COUNTIF(F9618:F9638,"H")[/TD]
[/TR]
[TR]
[TD]G9617[/TD]
[TD]=COUNTIF(G9618:G9638,"H")[/TD]
[/TR]
[TR]
[TD]H9617[/TD]
[TD]=COUNTIF(H9618:H9638,"H")[/TD]
[/TR]
[TR]
[TD]I9617[/TD]
[TD]=COUNTIF(I9618:I9638,"H")[/TD]
[/TR]
[TR]
[TD]J9617[/TD]
[TD]=COUNTIF(J9618:J9638,"H")[/TD]
[/TR]
[TR]
[TD]C9618[/TD]
[TD]=IF(A9618="","",VLOOKUP[color=#008000](A9618,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C9619[/TD]
[TD]=IF(A9619="","",VLOOKUP[color=#008000](A9619,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C9620[/TD]
[TD]=IF(A9620="","",VLOOKUP[color=#008000](A9620,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]B9621[/TD]
[TD]=IF(A9621="","",VLOOKUP[color=#008000](A9621,employees,2,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C9621[/TD]
[TD]=IF(A9621="","",VLOOKUP[color=#008000](A9621,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]B9622[/TD]
[TD]=IF(A9622="","",VLOOKUP[color=#008000](A9622,employees,2,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C9622[/TD]
[TD]=IF(A9622="","",VLOOKUP[color=#008000](A9622,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]B9623[/TD]
[TD]=IF(A9623="","",VLOOKUP[color=#008000](A9623,employees,2,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C9623[/TD]
[TD]=IF(A9623="","",VLOOKUP[color=#008000](A9623,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]B9624[/TD]
[TD]=IF(A9624="","",VLOOKUP[color=#008000](A9624,employees,2,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C9624[/TD]
[TD]=IF(A9624="","",VLOOKUP[color=#008000](A9624,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[URL="http://www.excel-jeanie-html.de/index.php?f=1"]Excel tables to the web - Excel Jeanie Html 4[/URL]