search function using either vlookup or index match or another function

M

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


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]
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
Hi Aladin thankyou for your responce once again, however after trying the posted template it works but changing to my cell range i could not get it to work
i have enclosed my first sample template, the second is your data with my cell range that did not work and the 3rd template is somthing how i would like the end result, would this ever be achievable using this method ?
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"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/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]Test1 Off [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #c0c0c0, align: center"]32[/TD]
[TD]Test2 Off[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/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"]0[/TD]
[TD="bgcolor: #c0c0c0, align: center"]2[/TD]
[TD="bgcolor: #c0c0c0, align: center"]1[/TD]
[TD="bgcolor: #c0c0c0, align: center"]0[/TD]
[TD="bgcolor: #c0c0c0, align: center"]0[/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="align: left"]littlewood[/TD]
[TD="align: center"]H[/TD]
[TD]*[/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"]55555[/TD]
[TD="align: left"]paul hold[/TD]
[TD="align: left"]littlewood[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD]*[/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"]3601[/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"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3602[/TD]
[TD="align: center"]WEEK[/TD]
[TD="align: center"]2014[/TD]
[TD]Test1 Off [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3603[/TD]
[TD="bgcolor: #c0c0c0, align: center"]44[/TD]
[TD]Test2 Off[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3604[/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"]3605[/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"]26-Oct[/TD]
[TD="bgcolor: #ffff00, align: center"]27-Oct[/TD]
[TD="bgcolor: #ffff00, align: center"]28-Oct[/TD]
[TD="bgcolor: #ffff00, align: center"]29-Oct[/TD]
[TD="bgcolor: #ffff00, align: center"]30-Oct[/TD]
[TD="bgcolor: #ffff00, align: center"]31-Oct[/TD]
[TD="bgcolor: #ffff00, align: center"]01-Nov[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3606[/TD]
[TD="bgcolor: #ff0000, colspan: 3, align: center"]PRINTING[/TD]
[TD="bgcolor: #c0c0c0, align: center"]2[/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"]2[/TD]
[TD="bgcolor: #c0c0c0, align: center"]1[/TD]
[TD="bgcolor: #c0c0c0, align: center"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3607[/TD]
[TD="align: left"]33333[/TD]
[TD="align: left"]peter clarke[/TD]
[TD="align: left"]littlewood[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3608[/TD]
[TD="align: left"]44444[/TD]
[TD="align: left"]sid little[/TD]
[TD="align: left"]littlewood[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3609[/TD]
[TD="align: left"]11111[/TD]
[TD="align: left"]joe bloggs[/TD]
[TD="align: left"]littlewood[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3610[/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"]3611[/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"]14101[/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"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]14102[/TD]
[TD="align: center"]WEEK[/TD]
[TD="align: center"]2015[/TD]
[TD]Test1 Off [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]14103[/TD]
[TD="bgcolor: #c0c0c0, align: center"]27[/TD]
[TD]Test2 Off[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]14104[/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"]14105[/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"]28-Jun[/TD]
[TD="bgcolor: #ffff00, align: center"]29-Jun[/TD]
[TD="bgcolor: #ffff00, align: center"]30-Jun[/TD]
[TD="bgcolor: #ffff00, align: center"]01-Jul[/TD]
[TD="bgcolor: #ffff00, align: center"]02-Jul[/TD]
[TD="bgcolor: #ffff00, align: center"]03-Jul[/TD]
[TD="bgcolor: #ffff00, align: center"]04-Jul[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]14106[/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"]0[/TD]
[TD="bgcolor: #c0c0c0, align: center"]1[/TD]
[TD="bgcolor: #c0c0c0, align: center"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]14107[/TD]
[TD="align: left"]66666[/TD]
[TD="align: left"]sally bride[/TD]
[TD="align: left"]littlewood[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]14108[/TD]
[TD="align: left"]11111[/TD]
[TD="align: left"]joe bloggs[/TD]
[TD="align: left"]littlewood[/TD]
[TD]*[/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"]14109[/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:D63,"H")[/TD]
[/TR]
[TR]
[TD]E1[/TD]
[TD]=COUNTIF(E7:E63,"H")[/TD]
[/TR]
[TR]
[TD]F1[/TD]
[TD]=COUNTIF(F7:F63,"H")[/TD]
[/TR]
[TR]
[TD]G1[/TD]
[TD]=COUNTIF(G7:G63,"H")[/TD]
[/TR]
[TR]
[TD]H1[/TD]
[TD]=COUNTIF(H7:H63,"H")[/TD]
[/TR]
[TR]
[TD]I1[/TD]
[TD]=COUNTIF(I7:I63,"H")[/TD]
[/TR]
[TR]
[TD]J1[/TD]
[TD]=COUNTIF(J7:J63,"H")[/TD]
[/TR]
[TR]
[TD]D2[/TD]
[TD]=COUNTIFS($C7:$C63,"Test1",D7:D63,"H")[/TD]
[/TR]
[TR]
[TD]E2[/TD]
[TD]=COUNTIFS($C7:$C63,"Test1",E7:E63,"H")[/TD]
[/TR]
[TR]
[TD]F2[/TD]
[TD]=COUNTIFS($C7:$C63,"Test1",F7:F63,"H")[/TD]
[/TR]
[TR]
[TD]G2[/TD]
[TD]=COUNTIFS($C7:$C63,"Test1",G7:G63,"H")[/TD]
[/TR]
[TR]
[TD]H2[/TD]
[TD]=COUNTIFS($C7:$C63,"Test1",H7:H63,"H")[/TD]
[/TR]
[TR]
[TD]I2[/TD]
[TD]=COUNTIFS($C7:$C63,"Test1",I7:I63,"H")[/TD]
[/TR]
[TR]
[TD]J2[/TD]
[TD]=COUNTIFS($C7:$C63,"Test1",J7:J63,"H")[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]=WEEKNUM(J5,2)[/TD]
[/TR]
[TR]
[TD]D3[/TD]
[TD]=COUNTIFS($C7:$C63,"Test2",D7:D63,"H")[/TD]
[/TR]
[TR]
[TD]E3[/TD]
[TD]=COUNTIFS($C7:$C63,"Test2",E7:E63,"H")[/TD]
[/TR]
[TR]
[TD]F3[/TD]
[TD]=COUNTIFS($C7:$C63,"Test2",F7:F63,"H")[/TD]
[/TR]
[TR]
[TD]G3[/TD]
[TD]=COUNTIFS($C7:$C63,"Test2",G7:G63,"H")[/TD]
[/TR]
[TR]
[TD]H3[/TD]
[TD]=COUNTIFS($C7:$C63,"Test2",H7:H63,"H")[/TD]
[/TR]
[TR]
[TD]I3[/TD]
[TD]=COUNTIFS($C7:$C63,"Test2",I7:I63,"H")[/TD]
[/TR]
[TR]
[TD]J3[/TD]
[TD]=COUNTIFS($C7:$C63,"Test2",J7:J63,"H")[/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:D27,"H")[/TD]
[/TR]
[TR]
[TD]E6[/TD]
[TD]=COUNTIF(E7:E27,"H")[/TD]
[/TR]
[TR]
[TD]F6[/TD]
[TD]=COUNTIF(F7:F27,"H")[/TD]
[/TR]
[TR]
[TD]G6[/TD]
[TD]=COUNTIF(G7:G27,"H")[/TD]
[/TR]
[TR]
[TD]H6[/TD]
[TD]=COUNTIF(H7:H27,"H")[/TD]
[/TR]
[TR]
[TD]I6[/TD]
[TD]=COUNTIF(I7:I27,"H")[/TD]
[/TR]
[TR]
[TD]J6[/TD]
[TD]=COUNTIF(J7:J27,"H")[/TD]
[/TR]
[TR]
[TD]B7[/TD]
[TD]=IF(A7="","",VLOOKUP[color=#008000](A7,employees,2,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C7[/TD]
[TD]=IF(A7="","",VLOOKUP[color=#008000](A7,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]B8[/TD]
[TD]=IF(A8="","",VLOOKUP[color=#008000](A8,employees,2,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]D3601[/TD]
[TD]=COUNTIF(D3607:D3663,"H")[/TD]
[/TR]
[TR]
[TD]E3601[/TD]
[TD]=COUNTIF(E3607:E3663,"H")[/TD]
[/TR]
[TR]
[TD]F3601[/TD]
[TD]=COUNTIF(F3607:F3663,"H")[/TD]
[/TR]
[TR]
[TD]G3601[/TD]
[TD]=COUNTIF(G3607:G3663,"H")[/TD]
[/TR]
[TR]
[TD]H3601[/TD]
[TD]=COUNTIF(H3607:H3663,"H")[/TD]
[/TR]
[TR]
[TD]I3601[/TD]
[TD]=COUNTIF(I3607:I3663,"H")[/TD]
[/TR]
[TR]
[TD]J3601[/TD]
[TD]=COUNTIF(J3607:J3663,"H")[/TD]
[/TR]
[TR]
[TD]B3602[/TD]
[TD]=B3502[/TD]
[/TR]
[TR]
[TD]D3602[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test1",D3607:D3663,"H")[/TD]
[/TR]
[TR]
[TD]E3602[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test1",E3607:E3663,"H")[/TD]
[/TR]
[TR]
[TD]F3602[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test1",F3607:F3663,"H")[/TD]
[/TR]
[TR]
[TD]G3602[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test1",G3607:G3663,"H")[/TD]
[/TR]
[TR]
[TD]H3602[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test1",H3607:H3663,"H")[/TD]
[/TR]
[TR]
[TD]I3602[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test1",I3607:I3663,"H")[/TD]
[/TR]
[TR]
[TD]J3602[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test1",J3607:J3663,"H")[/TD]
[/TR]
[TR]
[TD]A3603[/TD]
[TD]=WEEKNUM(J3605,2)[/TD]
[/TR]
[TR]
[TD]D3603[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test2",D3607:D3663,"H")[/TD]
[/TR]
[TR]
[TD]E3603[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test2",E3607:E3663,"H")[/TD]
[/TR]
[TR]
[TD]F3603[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test2",F3607:F3663,"H")[/TD]
[/TR]
[TR]
[TD]G3603[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test2",G3607:G3663,"H")[/TD]
[/TR]
[TR]
[TD]H3603[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test2",H3607:H3663,"H")[/TD]
[/TR]
[TR]
[TD]I3603[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test2",I3607:I3663,"H")[/TD]
[/TR]
[TR]
[TD]J3603[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test2",J3607:J3663,"H")[/TD]
[/TR]
[TR]
[TD]D3605[/TD]
[TD]=J3505+1[/TD]
[/TR]
[TR]
[TD]E3605[/TD]
[TD]=+D3605+1[/TD]
[/TR]
[TR]
[TD]F3605[/TD]
[TD]=+E3605+1[/TD]
[/TR]
[TR]
[TD]G3605[/TD]
[TD]=+F3605+1[/TD]
[/TR]
[TR]
[TD]H3605[/TD]
[TD]=+G3605+1[/TD]
[/TR]
[TR]
[TD]I3605[/TD]
[TD]=+H3605+1[/TD]
[/TR]
[TR]
[TD]J3605[/TD]
[TD]=+I3605+1[/TD]
[/TR]
[TR]
[TD]D3606[/TD]
[TD]=COUNTIF(D3607:D3627,"H")[/TD]
[/TR]
[TR]
[TD]E3606[/TD]
[TD]=COUNTIF(E3607:E3627,"H")[/TD]
[/TR]
[TR]
[TD]F3606[/TD]
[TD]=COUNTIF(F3607:F3627,"H")[/TD]
[/TR]
[TR]
[TD]G3606[/TD]
[TD]=COUNTIF(G3607:G3627,"H")[/TD]
[/TR]
[TR]
[TD]H3606[/TD]
[TD]=COUNTIF(H3607:H3627,"H")[/TD]
[/TR]
[TR]
[TD]I3606[/TD]
[TD]=COUNTIF(I3607:I3627,"H")[/TD]
[/TR]
[TR]
[TD]J3606[/TD]
[TD]=COUNTIF(J3607:J3627,"H")[/TD]
[/TR]
[TR]
[TD]B3607[/TD]
[TD]=IF(A3607="","",VLOOKUP[color=#008000](A3607,employees,2,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C3607[/TD]
[TD]=IF(A3607="","",VLOOKUP[color=#008000](A3607,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]B3608[/TD]
[TD]=IF(A3608="","",VLOOKUP[color=#008000](A3608,employees,2,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C3608[/TD]
[TD]=IF(A3608="","",VLOOKUP[color=#008000](A3608,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]B3609[/TD]
[TD]=IF(A3609="","",VLOOKUP[color=#008000](A3609,employees,2,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C3609[/TD]
[TD]=IF(A3609="","",VLOOKUP[color=#008000](A3609,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]B3610[/TD]
[TD]=IF(A3610="","",VLOOKUP[color=#008000](A3610,employees,2,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C3610[/TD]
[TD]=IF(A3610="","",VLOOKUP[color=#008000](A3610,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]B3611[/TD]
[TD]=IF(A3611="","",VLOOKUP[color=#008000](A3611,employees,2,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C3611[/TD]
[TD]=IF(A3611="","",VLOOKUP[color=#008000](A3611,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]D14101[/TD]
[TD]=COUNTIF(D14107:D14163,"H")[/TD]
[/TR]
[TR]
[TD]E14101[/TD]
[TD]=COUNTIF(E14107:E14163,"H")[/TD]
[/TR]
[TR]
[TD]F14101[/TD]
[TD]=COUNTIF(F14107:F14163,"H")[/TD]
[/TR]
[TR]
[TD]G14101[/TD]
[TD]=COUNTIF(G14107:G14163,"H")[/TD]
[/TR]
[TR]
[TD]H14101[/TD]
[TD]=COUNTIF(H14107:H14163,"H")[/TD]
[/TR]
[TR]
[TD]I14101[/TD]
[TD]=COUNTIF(I14107:I14163,"H")[/TD]
[/TR]
[TR]
[TD]J14101[/TD]
[TD]=COUNTIF(J14107:J14163,"H")[/TD]
[/TR]
[TR]
[TD]B14102[/TD]
[TD]=B14002[/TD]
[/TR]
[TR]
[TD]D14102[/TD]
[TD]=COUNTIFS($C14107:$C14163,"Test1",D14107:D14163,"H")[/TD]
[/TR]
[TR]
[TD]E14102[/TD]
[TD]=COUNTIFS($C14107:$C14163,"Test1",E14107:E14163,"H")[/TD]
[/TR]
[TR]
[TD]F14102[/TD]
[TD]=COUNTIFS($C14107:$C14163,"Test1",F14107:F14163,"H")[/TD]
[/TR]
[TR]
[TD]G14102[/TD]
[TD]=COUNTIFS($C14107:$C14163,"Test1",G14107:G14163,"H")[/TD]
[/TR]
[TR]
[TD]H14102[/TD]
[TD]=COUNTIFS($C14107:$C14163,"Test1",H14107:H14163,"H")[/TD]
[/TR]
[TR]
[TD]I14102[/TD]
[TD]=COUNTIFS($C14107:$C14163,"Test1",I14107:I14163,"H")[/TD]
[/TR]
[TR]
[TD]J14102[/TD]
[TD]=COUNTIFS($C14107:$C14163,"Test1",J14107:J14163,"H")[/TD]
[/TR]
[TR]
[TD]A14103[/TD]
[TD]=WEEKNUM(J14105,2)[/TD]
[/TR]
[TR]
[TD]D14103[/TD]
[TD]=COUNTIFS($C14107:$C14163,"Test2",D14107:D14163,"H")[/TD]
[/TR]
[TR]
[TD]E14103[/TD]
[TD]=COUNTIFS($C14107:$C14163,"Test2",E14107:E14163,"H")[/TD]
[/TR]
[TR]
[TD]F14103[/TD]
[TD]=COUNTIFS($C14107:$C14163,"Test2",F14107:F14163,"H")[/TD]
[/TR]
[TR]
[TD]G14103[/TD]
[TD]=COUNTIFS($C14107:$C14163,"Test2",G14107:G14163,"H")[/TD]
[/TR]
[TR]
[TD]H14103[/TD]
[TD]=COUNTIFS($C14107:$C14163,"Test2",H14107:H14163,"H")[/TD]
[/TR]
[TR]
[TD]I14103[/TD]
[TD]=COUNTIFS($C14107:$C14163,"Test2",I14107:I14163,"H")[/TD]
[/TR]
[TR]
[TD]J14103[/TD]
[TD]=COUNTIFS($C14107:$C14163,"Test2",J14107:J14163,"H")[/TD]
[/TR]
[TR]
[TD]D14105[/TD]
[TD]=J14005+1[/TD]
[/TR]
[TR]
[TD]E14105[/TD]
[TD]=+D14105+1[/TD]
[/TR]
[TR]
[TD]F14105[/TD]
[TD]=+E14105+1[/TD]
[/TR]
[TR]
[TD]G14105[/TD]
[TD]=+F14105+1[/TD]
[/TR]
[TR]
[TD]H14105[/TD]
[TD]=+G14105+1[/TD]
[/TR]
[TR]
[TD]I14105[/TD]
[TD]=+H14105+1[/TD]
[/TR]
[TR]
[TD]J14105[/TD]
[TD]=+I14105+1[/TD]
[/TR]
[TR]
[TD]D14106[/TD]
[TD]=COUNTIF(D14107:D14127,"H")[/TD]
[/TR]
[TR]
[TD]E14106[/TD]
[TD]=COUNTIF(E14107:E14127,"H")[/TD]
[/TR]
[TR]
[TD]F14106[/TD]
[TD]=COUNTIF(F14107:F14127,"H")[/TD]
[/TR]
[TR]
[TD]G14106[/TD]
[TD]=COUNTIF(G14107:G14127,"H")[/TD]
[/TR]
[TR]
[TD]H14106[/TD]
[TD]=COUNTIF(H14107:H14127,"H")[/TD]
[/TR]
[TR]
[TD]I14106[/TD]
[TD]=COUNTIF(I14107:I14127,"H")[/TD]
[/TR]
[TR]
[TD]J14106[/TD]
[TD]=COUNTIF(J14107:J14127,"H")[/TD]
[/TR]
[TR]
[TD]B14107[/TD]
[TD]=IF(A14107="","",VLOOKUP[color=#008000](A14107,employees,2,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C14107[/TD]
[TD]=IF(A14107="","",VLOOKUP[color=#008000](A14107,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]B14108[/TD]
[TD]=IF(A14108="","",VLOOKUP[color=#008000](A14108,employees,2,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C14108[/TD]
[TD]=IF(A14108="","",VLOOKUP[color=#008000](A14108,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]B14109[/TD]
[TD]=IF(A14109="","",VLOOKUP[color=#008000](A14109,employees,2,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C14109[/TD]
[TD]=IF(A14109="","",VLOOKUP[color=#008000](A14109,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]

Code: 
[B]Overiew[/B]

[TABLE]
 <colgroup><col style="font-weight:bold; width:30px;  "><col style="width:119px;"><col style="width:80px;"><col style="width:80px;"><col style="width:80px;"><col style="width:80px;"><col style="width:80px;"><col style="width:80px;"><col style="width:80px;"><col style="width:80px;"><col style="width:80px;"></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"]12[/TD]
[TD="align: center"]associte No[/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"]13[/TD]
[TD="align: center"]11111[/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"]14[/TD]
[TD="align: center"]3[/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"]15[/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"]16[/TD]
[TD="align: center"]7[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: center"]3609[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: center"]14108[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]22[/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/TD]
[TD]#NUM![/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]A14[/TD]
[TD]=COUNTIF('YTD Holidays'!$A$1:$A$23000,A13)[/TD]
[/TR]
[TR]
[TD]A16[/TD]
[TD]{=IF(ROWS[color=#008000]($A$16:$A$16)[/COLOR]<=$A$14,SMALL[color=#008000](IF[COLOR=#0000ff]('YTD Holidays'!$A$1:$A$23000=$A$13,ROW[COLOR=#ff0000]('YTD Holidays'!$A$1:$A$23000)[/COLOR]-ROW[COLOR=#ff0000]('YTD Holidays'!$A$1)[/COLOR]+1)[/COLOR],ROWS[COLOR=#0000ff]($A$16:A16)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]B16[/TD]
[TD]{=IF(N[color=#008000]($A16)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A16,MATCH[COLOR=#0000ff](B$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]C16[/TD]
[TD]{=IF(N[color=#008000]($A16)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A16,MATCH[COLOR=#0000ff](C$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]D16[/TD]
[TD]{=IF(N[color=#008000]($A16)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A16,MATCH[COLOR=#0000ff](D$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]E16[/TD]
[TD]{=IF(N[color=#008000]($A16)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A16,MATCH[COLOR=#0000ff](E$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]F16[/TD]
[TD]{=IF(N[color=#008000]($A16)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A16,MATCH[COLOR=#0000ff](F$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]G16[/TD]
[TD]{=IF(N[color=#008000]($A16)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A16,MATCH[COLOR=#0000ff](G$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]H16[/TD]
[TD]{=IF(N[color=#008000]($A16)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A16,MATCH[COLOR=#0000ff](H$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]I16[/TD]
[TD]{=IF(N[color=#008000]($A16)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A16,MATCH[COLOR=#0000ff](I$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]J16[/TD]
[TD]{=IF(N[color=#008000]($A16)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A16,MATCH[COLOR=#0000ff](J$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]A17[/TD]
[TD]{=IF(ROWS[color=#008000]($A$16:$A$16)[/COLOR]<=$A$14,SMALL[color=#008000](IF[COLOR=#0000ff]('YTD Holidays'!$A$1:$A$23000=$A$13,ROW[COLOR=#ff0000]('YTD Holidays'!$A$1:$A$23000)[/COLOR]-ROW[COLOR=#ff0000]('YTD Holidays'!$A$1)[/COLOR]+1)[/COLOR],ROWS[COLOR=#0000ff]($A$16:A17)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]B17[/TD]
[TD]{=IF(N[color=#008000]($A17)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A17,MATCH[COLOR=#0000ff](B$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]C17[/TD]
[TD]{=IF(N[color=#008000]($A17)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A17,MATCH[COLOR=#0000ff](C$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]D17[/TD]
[TD]{=IF(N[color=#008000]($A17)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A17,MATCH[COLOR=#0000ff](D$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]E17[/TD]
[TD]{=IF(N[color=#008000]($A17)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A17,MATCH[COLOR=#0000ff](E$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]F17[/TD]
[TD]{=IF(N[color=#008000]($A17)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A17,MATCH[COLOR=#0000ff](F$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]G17[/TD]
[TD]{=IF(N[color=#008000]($A17)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A17,MATCH[COLOR=#0000ff](G$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]H17[/TD]
[TD]{=IF(N[color=#008000]($A17)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A17,MATCH[COLOR=#0000ff](H$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]I17[/TD]
[TD]{=IF(N[color=#008000]($A17)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A17,MATCH[COLOR=#0000ff](I$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]J17[/TD]
[TD]{=IF(N[color=#008000]($A17)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A17,MATCH[COLOR=#0000ff](J$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]A18[/TD]
[TD]{=IF(ROWS[color=#008000]($A$16:$A$16)[/COLOR]<=$A$14,SMALL[color=#008000](IF[COLOR=#0000ff]('YTD Holidays'!$A$1:$A$23000=$A$13,ROW[COLOR=#ff0000]('YTD Holidays'!$A$1:$A$23000)[/COLOR]-ROW[COLOR=#ff0000]('YTD Holidays'!$A$1)[/COLOR]+1)[/COLOR],ROWS[COLOR=#0000ff]($A$16:A18)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]B18[/TD]
[TD]{=IF(N[color=#008000]($A18)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A18,MATCH[COLOR=#0000ff](B$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]C18[/TD]
[TD]{=IF(N[color=#008000]($A18)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A18,MATCH[COLOR=#0000ff](C$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]D18[/TD]
[TD]{=IF(N[color=#008000]($A18)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A18,MATCH[COLOR=#0000ff](D$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]E18[/TD]
[TD]{=IF(N[color=#008000]($A18)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A18,MATCH[COLOR=#0000ff](E$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]F18[/TD]
[TD]{=IF(N[color=#008000]($A18)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A18,MATCH[COLOR=#0000ff](F$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]G18[/TD]
[TD]{=IF(N[color=#008000]($A18)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A18,MATCH[COLOR=#0000ff](G$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]H18[/TD]
[TD]{=IF(N[color=#008000]($A18)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A18,MATCH[COLOR=#0000ff](H$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]I18[/TD]
[TD]{=IF(N[color=#008000]($A18)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A18,MATCH[COLOR=#0000ff](I$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]J18[/TD]
[TD]{=IF(N[color=#008000]($A18)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A18,MATCH[COLOR=#0000ff](J$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]A19[/TD]
[TD]{=IF(ROWS[color=#008000]($A$16:$A$16)[/COLOR]<=$A$14,SMALL[color=#008000](IF[COLOR=#0000ff]('YTD Holidays'!$A$1:$A$23000=$A$13,ROW[COLOR=#ff0000]('YTD Holidays'!$A$1:$A$23000)[/COLOR]-ROW[COLOR=#ff0000]('YTD Holidays'!$A$1)[/COLOR]+1)[/COLOR],ROWS[COLOR=#0000ff]($A$16:A19)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]B19[/TD]
[TD]{=IF(N[color=#008000]($A19)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A19,MATCH[COLOR=#0000ff](B$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]C19[/TD]
[TD]{=IF(N[color=#008000]($A19)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A19,MATCH[COLOR=#0000ff](C$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]D19[/TD]
[TD]{=IF(N[color=#008000]($A19)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A19,MATCH[COLOR=#0000ff](D$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]E19[/TD]
[TD]{=IF(N[color=#008000]($A19)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A19,MATCH[COLOR=#0000ff](E$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]F19[/TD]
[TD]{=IF(N[color=#008000]($A19)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A19,MATCH[COLOR=#0000ff](F$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]G19[/TD]
[TD]{=IF(N[color=#008000]($A19)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A19,MATCH[COLOR=#0000ff](G$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]H19[/TD]
[TD]{=IF(N[color=#008000]($A19)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A19,MATCH[COLOR=#0000ff](H$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]I19[/TD]
[TD]{=IF(N[color=#008000]($A19)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A19,MATCH[COLOR=#0000ff](I$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]J19[/TD]
[TD]{=IF(N[color=#008000]($A19)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A19,MATCH[COLOR=#0000ff](J$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]A20[/TD]
[TD]{=IF(ROWS[color=#008000]($A$16:$A$16)[/COLOR]<=$A$14,SMALL[color=#008000](IF[COLOR=#0000ff]('YTD Holidays'!$A$1:$A$23000=$A$13,ROW[COLOR=#ff0000]('YTD Holidays'!$A$1:$A$23000)[/COLOR]-ROW[COLOR=#ff0000]('YTD Holidays'!$A$1)[/COLOR]+1)[/COLOR],ROWS[COLOR=#0000ff]($A$16:A20)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]B20[/TD]
[TD]{=IF(N[color=#008000]($A20)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A20,MATCH[COLOR=#0000ff](B$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]C20[/TD]
[TD]{=IF(N[color=#008000]($A20)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A20,MATCH[COLOR=#0000ff](C$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]D20[/TD]
[TD]{=IF(N[color=#008000]($A20)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A20,MATCH[COLOR=#0000ff](D$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]E20[/TD]
[TD]{=IF(N[color=#008000]($A20)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A20,MATCH[COLOR=#0000ff](E$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]F20[/TD]
[TD]{=IF(N[color=#008000]($A20)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A20,MATCH[COLOR=#0000ff](F$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]G20[/TD]
[TD]{=IF(N[color=#008000]($A20)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A20,MATCH[COLOR=#0000ff](G$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]H20[/TD]
[TD]{=IF(N[color=#008000]($A20)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A20,MATCH[COLOR=#0000ff](H$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]I20[/TD]
[TD]{=IF(N[color=#008000]($A20)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A20,MATCH[COLOR=#0000ff](I$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]J20[/TD]
[TD]{=IF(N[color=#008000]($A20)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A20,MATCH[COLOR=#0000ff](J$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]A21[/TD]
[TD]{=IF(ROWS[color=#008000]($A$16:$A$16)[/COLOR]<=$A$14,SMALL[color=#008000](IF[COLOR=#0000ff]('YTD Holidays'!$A$1:$A$23000=$A$13,ROW[COLOR=#ff0000]('YTD Holidays'!$A$1:$A$23000)[/COLOR]-ROW[COLOR=#ff0000]('YTD Holidays'!$A$1)[/COLOR]+1)[/COLOR],ROWS[COLOR=#0000ff]($A$16:A21)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]B21[/TD]
[TD]{=IF(N[color=#008000]($A21)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A21,MATCH[COLOR=#0000ff](B$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]C21[/TD]
[TD]{=IF(N[color=#008000]($A21)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A21,MATCH[COLOR=#0000ff](C$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]D21[/TD]
[TD]{=IF(N[color=#008000]($A21)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A21,MATCH[COLOR=#0000ff](D$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]E21[/TD]
[TD]{=IF(N[color=#008000]($A21)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A21,MATCH[COLOR=#0000ff](E$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]F21[/TD]
[TD]{=IF(N[color=#008000]($A21)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A21,MATCH[COLOR=#0000ff](F$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]G21[/TD]
[TD]{=IF(N[color=#008000]($A21)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A21,MATCH[COLOR=#0000ff](G$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]H21[/TD]
[TD]{=IF(N[color=#008000]($A21)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A21,MATCH[COLOR=#0000ff](H$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]I21[/TD]
[TD]{=IF(N[color=#008000]($A21)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A21,MATCH[COLOR=#0000ff](I$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]J21[/TD]
[TD]{=IF(N[color=#008000]($A21)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A21,MATCH[COLOR=#0000ff](J$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]A22[/TD]
[TD]{=IF(ROWS[color=#008000]($A$16:$A$16)[/COLOR]<=$A$14,SMALL[color=#008000](IF[COLOR=#0000ff]('YTD Holidays'!$A$1:$A$23000=$A$13,ROW[COLOR=#ff0000]('YTD Holidays'!$A$1:$A$23000)[/COLOR]-ROW[COLOR=#ff0000]('YTD Holidays'!$A$1)[/COLOR]+1)[/COLOR],ROWS[COLOR=#0000ff]($A$16:A22)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]B22[/TD]
[TD]{=IF(N[color=#008000]($A22)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A22,MATCH[COLOR=#0000ff](B$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]C22[/TD]
[TD]{=IF(N[color=#008000]($A22)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A22,MATCH[COLOR=#0000ff](C$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]D22[/TD]
[TD]{=IF(N[color=#008000]($A22)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A22,MATCH[COLOR=#0000ff](D$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]E22[/TD]
[TD]{=IF(N[color=#008000]($A22)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A22,MATCH[COLOR=#0000ff](E$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]F22[/TD]
[TD]{=IF(N[color=#008000]($A22)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A22,MATCH[COLOR=#0000ff](F$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]G22[/TD]
[TD]{=IF(N[color=#008000]($A22)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A22,MATCH[COLOR=#0000ff](G$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]H22[/TD]
[TD]{=IF(N[color=#008000]($A22)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A22,MATCH[COLOR=#0000ff](H$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]I22[/TD]
[TD]{=IF(N[color=#008000]($A22)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A22,MATCH[COLOR=#0000ff](I$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
[TR]
[TD]J22[/TD]
[TD]{=IF(N[color=#008000]($A22)[/COLOR],INDEX[color=#008000]('YTD Holidays'!$B$1:$J$23000,$A22,MATCH[COLOR=#0000ff](J$15,'YTD Holidays'!$D$7:$J$7,0)[/COLOR])[/COLOR],"")}[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][FONT=Arial][B]Formula Array:[/B][/FONT][FONT=Arial]
Produce enclosing [/FONT][FONT=Arial][B]{ }[/B][/FONT][FONT=Arial] by entering
formula with CTRL+SHIFT+ENTER![/FONT][/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]

Code: 
[B]Overiew[/B]

[TABLE]
 <colgroup><col style="font-weight:bold; width:30px;  "><col style="width:119px;"><col style="width:80px;"><col style="width:80px;"><col style="width:80px;"><col style="width:80px;"><col style="width:80px;"><col style="width:80px;"><col style="width:80px;"><col style="width:80px;"><col style="width:80px;"></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"]26[/TD]
[TD="align: center"]associte No[/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"]27[/TD]
[TD="align: center"]11111[/TD]
[TD]*[/TD]
[TD]*[/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"]28[/TD]
[TD="align: center"]3[/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"]29[/TD]
[TD="align: center"]week No[/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"]30[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/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"]31[/TD]
[TD="align: center"]32[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]32[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="bgcolor: #ffff00, align: center"]26/10/201[/TD]
[TD="bgcolor: #ffff00, align: center"]26/10/202[/TD]
[TD="bgcolor: #ffff00, align: center"]26/10/203[/TD]
[TD="bgcolor: #ffff00, align: center"]26/10/204[/TD]
[TD="bgcolor: #ffff00, align: center"]26/10/205[/TD]
[TD="bgcolor: #ffff00, align: center"]26/10/206[/TD]
[TD="bgcolor: #ffff00, align: center"]26/10/207[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]33[/TD]
[TD="align: center"]44[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]34[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="bgcolor: #ffff00, align: center"]28-Jun[/TD]
[TD="bgcolor: #ffff00, align: center"]29-Jun[/TD]
[TD="bgcolor: #ffff00, align: center"]30-Jun[/TD]
[TD="bgcolor: #ffff00, align: center"]01-Jul[/TD]
[TD="bgcolor: #ffff00, align: center"]02-Jul[/TD]
[TD="bgcolor: #ffff00, align: center"]03-Jul[/TD]
[TD="bgcolor: #ffff00, align: center"]04-Jul[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]35[/TD]
[TD="align: center"]27[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/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"]36[/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]E30[/TD]
[TD]=+D30+1[/TD]
[/TR]
[TR]
[TD]F30[/TD]
[TD]=+E30+1[/TD]
[/TR]
[TR]
[TD]G30[/TD]
[TD]=+F30+1[/TD]
[/TR]
[TR]
[TD]H30[/TD]
[TD]=+G30+1[/TD]
[/TR]
[TR]
[TD]I30[/TD]
[TD]=+H30+1[/TD]
[/TR]
[TR]
[TD]J30[/TD]
[TD]=+I30+1[/TD]
[/TR]
[TR]
[TD]E34[/TD]
[TD]=+D34+1[/TD]
[/TR]
[TR]
[TD]F34[/TD]
[TD]=+E34+1[/TD]
[/TR]
[TR]
[TD]G34[/TD]
[TD]=+F34+1[/TD]
[/TR]
[TR]
[TD]H34[/TD]
[TD]=+G34+1[/TD]
[/TR]
[TR]
[TD]I34[/TD]
[TD]=+H34+1[/TD]
[/TR]
[TR]
[TD]J34[/TD]
[TD]=+I34+1[/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]
 
Upvote 0
Aladin Akyurek said:
Try to post a scaled-down, very small (uncolored) sample along with the expected output.
Click to expand...

Hi Aladin, you always seem to come to my rescue, scaled down version withou colour, i can email if you rquire

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"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/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]Test1 Off [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]32[/TD]
[TD]Test2 Off[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]DAYS *[/TD]
[TD="align: center"]Employee[/TD]
[TD]*[/TD]
[TD="align: center"]Sun[/TD]
[TD="align: center"]Mon[/TD]
[TD="align: center"]Tue[/TD]
[TD="align: center"]Wed[/TD]
[TD="align: center"]Thu[/TD]
[TD="align: center"]Fri[/TD]
[TD="align: center"]Sat[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]ASSOCIATE No[/TD]
[TD="align: center"]NAME[/TD]
[TD]*[/TD]
[TD="align: center"]03-Aug[/TD]
[TD="align: center"]04-Aug[/TD]
[TD="align: center"]05-Aug[/TD]
[TD="align: center"]06-Aug[/TD]
[TD="align: center"]07-Aug[/TD]
[TD="align: center"]08-Aug[/TD]
[TD="align: center"]09-Aug[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="colspan: 3, align: center"]PRINTING[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="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="align: left"]littlewood[/TD]
[TD="align: center"]H[/TD]
[TD]*[/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"]55555[/TD]
[TD="align: left"]paul hold[/TD]
[TD="align: left"]littlewood[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD]*[/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"]3601[/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"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3602[/TD]
[TD="align: center"]WEEK[/TD]
[TD="align: center"]2014[/TD]
[TD]Test1 Off [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3603[/TD]
[TD="align: center"]44[/TD]
[TD]Test2 Off[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3604[/TD]
[TD="align: center"]DAYS *[/TD]
[TD="align: center"]Employee[/TD]
[TD]*[/TD]
[TD="align: center"]Sun[/TD]
[TD="align: center"]Mon[/TD]
[TD="align: center"]Tue[/TD]
[TD="align: center"]Wed[/TD]
[TD="align: center"]Thu[/TD]
[TD="align: center"]Fri[/TD]
[TD="align: center"]Sat[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3605[/TD]
[TD="align: center"]ASSOCIATE No[/TD]
[TD="align: center"]NAME[/TD]
[TD]*[/TD]
[TD="align: center"]26-Oct[/TD]
[TD="align: center"]27-Oct[/TD]
[TD="align: center"]28-Oct[/TD]
[TD="align: center"]29-Oct[/TD]
[TD="align: center"]30-Oct[/TD]
[TD="align: center"]31-Oct[/TD]
[TD="align: center"]01-Nov[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3606[/TD]
[TD="colspan: 3, align: center"]PRINTING[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3607[/TD]
[TD="align: left"]33333[/TD]
[TD="align: left"]peter clarke[/TD]
[TD="align: left"]littlewood[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3608[/TD]
[TD="align: left"]44444[/TD]
[TD="align: left"]sid little[/TD]
[TD="align: left"]littlewood[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3609[/TD]
[TD="align: left"]11111[/TD]
[TD="align: left"]joe bloggs[/TD]
[TD="align: left"]littlewood[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3610[/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"]3611[/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:D63,"H")[/TD]
[/TR]
[TR]
[TD]E1[/TD]
[TD]=COUNTIF(E7:E63,"H")[/TD]
[/TR]
[TR]
[TD]F1[/TD]
[TD]=COUNTIF(F7:F63,"H")[/TD]
[/TR]
[TR]
[TD]G1[/TD]
[TD]=COUNTIF(G7:G63,"H")[/TD]
[/TR]
[TR]
[TD]H1[/TD]
[TD]=COUNTIF(H7:H63,"H")[/TD]
[/TR]
[TR]
[TD]I1[/TD]
[TD]=COUNTIF(I7:I63,"H")[/TD]
[/TR]
[TR]
[TD]J1[/TD]
[TD]=COUNTIF(J7:J63,"H")[/TD]
[/TR]
[TR]
[TD]D2[/TD]
[TD]=COUNTIFS($C7:$C63,"Test1",D7:D63,"H")[/TD]
[/TR]
[TR]
[TD]E2[/TD]
[TD]=COUNTIFS($C7:$C63,"Test1",E7:E63,"H")[/TD]
[/TR]
[TR]
[TD]F2[/TD]
[TD]=COUNTIFS($C7:$C63,"Test1",F7:F63,"H")[/TD]
[/TR]
[TR]
[TD]G2[/TD]
[TD]=COUNTIFS($C7:$C63,"Test1",G7:G63,"H")[/TD]
[/TR]
[TR]
[TD]H2[/TD]
[TD]=COUNTIFS($C7:$C63,"Test1",H7:H63,"H")[/TD]
[/TR]
[TR]
[TD]I2[/TD]
[TD]=COUNTIFS($C7:$C63,"Test1",I7:I63,"H")[/TD]
[/TR]
[TR]
[TD]J2[/TD]
[TD]=COUNTIFS($C7:$C63,"Test1",J7:J63,"H")[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]=WEEKNUM(J5,2)[/TD]
[/TR]
[TR]
[TD]D3[/TD]
[TD]=COUNTIFS($C7:$C63,"Test2",D7:D63,"H")[/TD]
[/TR]
[TR]
[TD]E3[/TD]
[TD]=COUNTIFS($C7:$C63,"Test2",E7:E63,"H")[/TD]
[/TR]
[TR]
[TD]F3[/TD]
[TD]=COUNTIFS($C7:$C63,"Test2",F7:F63,"H")[/TD]
[/TR]
[TR]
[TD]G3[/TD]
[TD]=COUNTIFS($C7:$C63,"Test2",G7:G63,"H")[/TD]
[/TR]
[TR]
[TD]H3[/TD]
[TD]=COUNTIFS($C7:$C63,"Test2",H7:H63,"H")[/TD]
[/TR]
[TR]
[TD]I3[/TD]
[TD]=COUNTIFS($C7:$C63,"Test2",I7:I63,"H")[/TD]
[/TR]
[TR]
[TD]J3[/TD]
[TD]=COUNTIFS($C7:$C63,"Test2",J7:J63,"H")[/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:D27,"H")[/TD]
[/TR]
[TR]
[TD]E6[/TD]
[TD]=COUNTIF(E7:E27,"H")[/TD]
[/TR]
[TR]
[TD]F6[/TD]
[TD]=COUNTIF(F7:F27,"H")[/TD]
[/TR]
[TR]
[TD]G6[/TD]
[TD]=COUNTIF(G7:G27,"H")[/TD]
[/TR]
[TR]
[TD]H6[/TD]
[TD]=COUNTIF(H7:H27,"H")[/TD]
[/TR]
[TR]
[TD]I6[/TD]
[TD]=COUNTIF(I7:I27,"H")[/TD]
[/TR]
[TR]
[TD]J6[/TD]
[TD]=COUNTIF(J7:J27,"H")[/TD]
[/TR]
[TR]
[TD]B7[/TD]
[TD]=IF(A7="","",VLOOKUP[color=#008000](A7,employees,2,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C7[/TD]
[TD]=IF(A7="","",VLOOKUP[color=#008000](A7,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]B8[/TD]
[TD]=IF(A8="","",VLOOKUP[color=#008000](A8,employees,2,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]D3601[/TD]
[TD]=COUNTIF(D3607:D3663,"H")[/TD]
[/TR]
[TR]
[TD]E3601[/TD]
[TD]=COUNTIF(E3607:E3663,"H")[/TD]
[/TR]
[TR]
[TD]F3601[/TD]
[TD]=COUNTIF(F3607:F3663,"H")[/TD]
[/TR]
[TR]
[TD]G3601[/TD]
[TD]=COUNTIF(G3607:G3663,"H")[/TD]
[/TR]
[TR]
[TD]H3601[/TD]
[TD]=COUNTIF(H3607:H3663,"H")[/TD]
[/TR]
[TR]
[TD]I3601[/TD]
[TD]=COUNTIF(I3607:I3663,"H")[/TD]
[/TR]
[TR]
[TD]J3601[/TD]
[TD]=COUNTIF(J3607:J3663,"H")[/TD]
[/TR]
[TR]
[TD]B3602[/TD]
[TD]=B3502[/TD]
[/TR]
[TR]
[TD]D3602[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test1",D3607:D3663,"H")[/TD]
[/TR]
[TR]
[TD]E3602[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test1",E3607:E3663,"H")[/TD]
[/TR]
[TR]
[TD]F3602[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test1",F3607:F3663,"H")[/TD]
[/TR]
[TR]
[TD]G3602[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test1",G3607:G3663,"H")[/TD]
[/TR]
[TR]
[TD]H3602[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test1",H3607:H3663,"H")[/TD]
[/TR]
[TR]
[TD]I3602[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test1",I3607:I3663,"H")[/TD]
[/TR]
[TR]
[TD]J3602[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test1",J3607:J3663,"H")[/TD]
[/TR]
[TR]
[TD]A3603[/TD]
[TD]=WEEKNUM(J3605,2)[/TD]
[/TR]
[TR]
[TD]D3603[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test2",D3607:D3663,"H")[/TD]
[/TR]
[TR]
[TD]E3603[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test2",E3607:E3663,"H")[/TD]
[/TR]
[TR]
[TD]F3603[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test2",F3607:F3663,"H")[/TD]
[/TR]
[TR]
[TD]G3603[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test2",G3607:G3663,"H")[/TD]
[/TR]
[TR]
[TD]H3603[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test2",H3607:H3663,"H")[/TD]
[/TR]
[TR]
[TD]I3603[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test2",I3607:I3663,"H")[/TD]
[/TR]
[TR]
[TD]J3603[/TD]
[TD]=COUNTIFS($C3607:$C3663,"Test2",J3607:J3663,"H")[/TD]
[/TR]
[TR]
[TD]D3605[/TD]
[TD]=J3505+1[/TD]
[/TR]
[TR]
[TD]E3605[/TD]
[TD]=+D3605+1[/TD]
[/TR]
[TR]
[TD]F3605[/TD]
[TD]=+E3605+1[/TD]
[/TR]
[TR]
[TD]G3605[/TD]
[TD]=+F3605+1[/TD]
[/TR]
[TR]
[TD]H3605[/TD]
[TD]=+G3605+1[/TD]
[/TR]
[TR]
[TD]I3605[/TD]
[TD]=+H3605+1[/TD]
[/TR]
[TR]
[TD]J3605[/TD]
[TD]=+I3605+1[/TD]
[/TR]
[TR]
[TD]D3606[/TD]
[TD]=COUNTIF(D3607:D3627,"H")[/TD]
[/TR]
[TR]
[TD]E3606[/TD]
[TD]=COUNTIF(E3607:E3627,"H")[/TD]
[/TR]
[TR]
[TD]F3606[/TD]
[TD]=COUNTIF(F3607:F3627,"H")[/TD]
[/TR]
[TR]
[TD]G3606[/TD]
[TD]=COUNTIF(G3607:G3627,"H")[/TD]
[/TR]
[TR]
[TD]H3606[/TD]
[TD]=COUNTIF(H3607:H3627,"H")[/TD]
[/TR]
[TR]
[TD]I3606[/TD]
[TD]=COUNTIF(I3607:I3627,"H")[/TD]
[/TR]
[TR]
[TD]J3606[/TD]
[TD]=COUNTIF(J3607:J3627,"H")[/TD]
[/TR]
[TR]
[TD]B3607[/TD]
[TD]=IF(A3607="","",VLOOKUP[color=#008000](A3607,employees,2,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C3607[/TD]
[TD]=IF(A3607="","",VLOOKUP[color=#008000](A3607,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]B3608[/TD]
[TD]=IF(A3608="","",VLOOKUP[color=#008000](A3608,employees,2,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C3608[/TD]
[TD]=IF(A3608="","",VLOOKUP[color=#008000](A3608,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]B3609[/TD]
[TD]=IF(A3609="","",VLOOKUP[color=#008000](A3609,employees,2,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C3609[/TD]
[TD]=IF(A3609="","",VLOOKUP[color=#008000](A3609,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]B3610[/TD]
[TD]=IF(A3610="","",VLOOKUP[color=#008000](A3610,employees,2,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C3610[/TD]
[TD]=IF(A3610="","",VLOOKUP[color=#008000](A3610,employees,3,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]B3611[/TD]
[TD]=IF(A3611="","",VLOOKUP[color=#008000](A3611,employees,2,FALSE)[/COLOR])[/TD]
[/TR]
[TR]
[TD]C3611[/TD]
[TD]=IF(A3611="","",VLOOKUP[color=#008000](A3611,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]

Code: 
  [TABLE]
<tbody>[TR]
[TD="class: xl75"]associte   No[/TD]
[TD="class: xl76, width: 80"][/TD]
[TD="class: xl76, width: 80"][/TD]
[TD="class: xl76, width: 80"][/TD]
[TD="class: xl76, width: 80"][/TD]
[TD="class: xl76, width: 80"][/TD]
[TD="class: xl76, width: 80"][/TD]
[TD="class: xl76, width: 80"][/TD]
[TD="class: xl76, width: 80"][/TD]
[TD="class: xl76, width: 80"][/TD]
[/TR]
[TR]
[TD="class: xl75"]11111[/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl77"]Sun[/TD]
[TD="class: xl77"]Mon[/TD]
[TD="class: xl77"]Tue[/TD]
[TD="class: xl77"]Wed[/TD]
[TD="class: xl77"]Thu[/TD]
[TD="class: xl77"]Fri[/TD]
[TD="class: xl77"]Sat[/TD]
[/TR]
[TR]
[TD="class: xl75"]3[/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[/TR]
[TR]
[TD="class: xl75"]week No[/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[/TR]
[TR]
[TD="class: xl75"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl78"]27-Oct[/TD]
[TD="class: xl78"]28-Oct[/TD]
[TD="class: xl78"]29-Oct[/TD]
[TD="class: xl78"]30-Oct[/TD]
[TD="class: xl78"]31-Oct[/TD]
[TD="class: xl78"]01-Nov[/TD]
[TD="class: xl78"]02-Nov[/TD]
[/TR]
[TR]
[TD="class: xl75"]32[/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl79"]H[/TD]
[TD="class: xl79"][/TD]
[TD="class: xl79"]H[/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[/TR]
[TR]
[TD="class: xl75"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl78"]26-Oct[/TD]
[TD="class: xl78"]27-Oct[/TD]
[TD="class: xl78"]28-Oct[/TD]
[TD="class: xl78"]29-Oct[/TD]
[TD="class: xl78"]30-Oct[/TD]
[TD="class: xl78"]31-Oct[/TD]
[TD="class: xl78"]01-Nov[/TD]
[/TR]
[TR]
[TD="class: xl75"]44[/TD]
[TD="class: xl76"][/TD]
[TD="class: xl76"][/TD]
[TD="class: xl79"][/TD]
[TD="class: xl79"]H[/TD]
[TD="class: xl79"][/TD]
[TD="class: xl79"][/TD]
[TD="class: xl79"]H[/TD]
[TD="class: xl79"][/TD]
[TD="class: xl79"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
hi Aladin the dates i posted are incorect, i presume you guessed that already, basicaly i want a summery sheet that sucks the data out of ytd holidays sheet when i input the associte N0 ie 11111 to show week N0 ie 1-53 and show the dates that the person has booked the H=holiday for the year so associte no 11111 has booked week 32 3rd august & 5th august and in week 44 has booked 27th october & 30th october taken from the ytd holiday sheet if that makes any sense...regards Dean
Code: 
[B]Overiew[/B]

[TABLE]
<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"]26[/TD]
[TD="align: center"]associte No[/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"]27[/TD]
[TD="align: center"]11111[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: center"]Sun[/TD]
[TD="align: center"]Mon[/TD]
[TD="align: center"]Tue[/TD]
[TD="align: center"]Wed[/TD]
[TD="align: center"]Thu[/TD]
[TD="align: center"]Fri[/TD]
[TD="align: center"]Sat[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]28[/TD]
[TD="align: center"]3[/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"]29[/TD]
[TD="align: center"]week No[/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"]30[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: center"]03-Aug[/TD]
[TD="align: center"]04-Aug[/TD]
[TD="align: center"]05-Aug[/TD]
[TD="align: center"]06-Aug[/TD]
[TD="align: center"]07-Aug[/TD]
[TD="align: center"]08-Aug[/TD]
[TD="align: center"]09-Aug[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]31[/TD]
[TD="align: center"]32[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]32[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: center"]26-Oct[/TD]
[TD="align: center"]27-Oct[/TD]
[TD="align: center"]28-Oct[/TD]
[TD="align: center"]29-Oct[/TD]
[TD="align: center"]30-Oct[/TD]
[TD="align: center"]31-Oct[/TD]
[TD="align: center"]01-Nov[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]33[/TD]
[TD="align: center"]44[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: center"]H[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD="align: center"]H[/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]E30[/TD]
[TD]=+D30+1[/TD]
[/TR]
[TR]
[TD]F30[/TD]
[TD]=+E30+1[/TD]
[/TR]
[TR]
[TD]G30[/TD]
[TD]=+F30+1[/TD]
[/TR]
[TR]
[TD]H30[/TD]
[TD]=+G30+1[/TD]
[/TR]
[TR]
[TD]I30[/TD]
[TD]=+H30+1[/TD]
[/TR]
[TR]
[TD]J30[/TD]
[TD]=+I30+1[/TD]
[/TR]
[TR]
[TD]E32[/TD]
[TD]=D32+1[/TD]
[/TR]
[TR]
[TD]F32[/TD]
[TD]=E32+1[/TD]
[/TR]
[TR]
[TD]G32[/TD]
[TD]=F32+1[/TD]
[/TR]
[TR]
[TD]H32[/TD]
[TD]=G32+1[/TD]
[/TR]
[TR]
[TD]I32[/TD]
[TD]=H32+1[/TD]
[/TR]
[TR]
[TD]J32[/TD]
[TD]=I32+1[/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]
 
Last edited:
Upvote 0
Can anyone help me further with my quest, or is this not practicle to accomplish using index match methods ?
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
adding cells based on criteria across several columns
Replies
5
Views
108
Cubist
Cubist
A
  • Solved
AVERAGEIFS with 4 criteria
2
Replies
16
Views
347
Fluff
Fluff
SaraWitch
  • Solved
VLOOKUP formula with multiple IF statements
Replies
2
Views
497
SaraWitch
SaraWitch
gtd526
  • Question
search and answer only ODD rows
Replies
6
Views
159
gtd526
gtd526
gtd526
  • Question
Create List of Cell Values with Criteria
Replies
1
Views
243
gtd526
gtd526

Forum statistics

Threads
1,215,463
Messages
6,124,963
Members
449,200
Latest member
indiansth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top