Keeping count on a sheet that the data clears each day

[Scott Law]

I use to track and assign detentions for students at a high school. I scan student ID's at the door if they are late and the unique ID is used to vlookup students name and location throughout the day. I clear the sheet at the end of each day. My issue is I need a way to track the amount of times students have been assigned detentions (scanned at the door). Everything I have tried has failed because of the clearing of the sheet each night. Ideally I would like another worksheet within the workbook that tabulates the number of times they have been scanned over the year that runs by a macro or something similar.
Thank you for any ideas
Scott

 

[texasalynn]

Welcome to MrExcel Board....

so how is your spreadsheet showing the detentions? Post a small sample of your data

 

[Scott Law]

<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:.32in .25in .18in .25in; mso-header-margin:.5in; mso-footer-margin:.18in; mso-horizontal-page-align:center; mso-vertical-page-align:center;} td {padding:0px; mso-ignoreadding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-size:11.0pt; font-family:Calibri, sans-serif; mso-font-charset:0; text-align:center;} .xl64 {font-size:11.0pt; font-family:Calibri, sans-serif; mso-font-charset:0;} .xl65 {font-size:11.0pt; font-family:Calibri, sans-serif; mso-font-charset:0; text-align:center; border:.5pt solid windowtext;} .xl66 {font-size:11.0pt; font-weight:700; font-family:Calibri, sans-serif; mso-font-charset:0; text-align:center; border:.5pt solid windowtext;} .xl67 {border:.5pt solid windowtext;} .xl68 {font-size:11.0pt; font-family:Calibri, sans-serif; mso-font-charset:0; border-top:.5pt solid windowtext; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:none;} .xl69 {font-size:12.0pt; font-weight:700; font-style:italic; font-family:Calibri, sans-serif; mso-font-charset:0; text-align:center; border-top:.5pt solid windowtext; border-right:.5pt solid windowtext; border-bottom:none; border-left:.5pt solid windowtext;} .xl70 {border-top:.5pt solid windowtext; border-right:.5pt solid windowtext; border-bottom:.5pt solid black; border-left:.5pt solid windowtext;} --> </style> <table style="border-collapse: collapse; width: 759px; height: 213px;" border="0" cellpadding="0" cellspacing="0"> <col style="mso-width-source:userset;mso-width-alt:5083;width:139pt" width="139"> <col style="mso-width-source:userset;mso-width-alt:5193;width:142pt" width="142"> <col style="mso-width-source:userset;mso-width-alt:3401;width:93pt" width="93"> <col style="mso-width-source:userset;mso-width-alt:3803;width:104pt" width="104"> <col style="mso-width-source:userset;mso-width-alt:3730;width:102pt" width="102"> <tbody><tr style="mso-height-source:userset;height:17.0pt" height="17"> <td class="xl69" style="height:17.0pt;width:139pt" height="17" width="139">Student ID#</td> <td class="xl66" style="border-left:none;width:142pt" width="142">Name</td> <td class="xl66" style="border-left:none;width:93pt" width="93">1st</td> <td class="xl66" style="border-left:none;width:104pt" width="104">4th</td> <td class="xl66" style="border-left:none;width:102pt" width="102">6th</td> </tr> <tr style="mso-height-source:userset;height:17.0pt" height="17"> <td class="xl67" style="height:17.0pt" align="right" height="17">2008002644</td> <td class="xl68" style="border-top:none">Abao, Jannelle J.</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:#9C0006;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#FFC7CE;mso-pattern:black none">4</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:#9C6500;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#FFEB9C;mso-pattern:black none">21</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:#9C6500;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#FFEB9C;mso-pattern:black none">20</td> </tr> <tr style="mso-height-source:userset;height:17.0pt" height="17"> <td class="xl67" style="height:17.0pt;border-top:none" align="right" height="17">2007002023</td> <td class="xl68" style="border-top:none">Abram, Asia S.</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:#9C0006;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#FFC7CE;mso-pattern:black none">1</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:#9C6500;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#FFEB9C;mso-pattern:black none">25</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:#9C0006;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#FFC7CE;mso-pattern:black none">52</td> </tr> <tr style="mso-height-source:userset;height:17.0pt" height="17"> <td class="xl67" style="height:17.0pt;border-top:none" align="right" height="17">2007002145</td> <td class="xl68" style="border-top:none">Acevedo, Ignacio A.</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:#9C0006;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#FFC7CE;mso-pattern:black none">38</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:#9C0006;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#FFC7CE;mso-pattern:black none">Fuqua</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:#9C0006;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#FFC7CE;mso-pattern:black none">4</td> </tr> <tr style="mso-height-source:userset;height:17.0pt" height="17"> <td class="xl67" style="height:17.0pt;border-top:none" align="right" height="17">2001002927</td> <td class="xl68" style="border-top:none">Acosta, Nicole C.</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:#9C0006;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#FFC7CE;mso-pattern:black none">2</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:#006100;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#C6EFCE;mso-pattern:black none">31</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:#006100;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#C6EFCE;mso-pattern:black none">41</td> </tr> <tr style="mso-height-source:userset;height:17.0pt" height="17"> <td class="xl67" style="height:17.0pt;border-top:none" align="right" height="17">2001003396</td> <td class="xl68" style="border-top:none">Acuna, Sherry E.</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:#006100;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#C6EFCE;mso-pattern:black none">57</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:#9C0006;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#FFC7CE;mso-pattern:black none">47</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:#9C0006;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#FFC7CE;mso-pattern:black none">38</td> </tr> <tr style="mso-height-source:userset;height:17.0pt" height="17"> <td class="xl67" style="height:17.0pt;border-top:none" align="right" height="17">2009000466</td> <td class="xl68" style="border-top:none">Adams, Christian V.</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:#9C6500;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#FFEB9C;mso-pattern:black none">24</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:#006100;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#C6EFCE;mso-pattern:black none">45</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:#006100;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#C6EFCE;mso-pattern:black none">56</td> </tr> <tr style="mso-height-source:userset;height:17.0pt" height="17"> <td class="xl67" style="height:17.0pt;border-top:none" align="right" height="17">2009000475</td> <td class="xl68" style="border-top:none">Adams, Destiny M.</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:#9C0006;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#FFC7CE;mso-pattern:black none">38</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:#9C6500;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#FFEB9C;mso-pattern:black none">12</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:#006100;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#C6EFCE;mso-pattern:black none">62</td> </tr> <tr style="mso-height-source:userset;height:17.0pt" height="17"> <td class="xl67" style="height:17.0pt;border-top:none" align="right" height="17">2005003756</td> <td class="xl68" style="border-top:none">Afualo, Alice L.</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:#9C0006;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#FFC7CE;mso-pattern:black none">48</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:#9C0006;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#FFC7CE;mso-pattern:black none">Bignami</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:#006100;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#C6EFCE;mso-pattern:black none">42</td> </tr> <tr style="mso-height-source:userset;height:17.0pt" height="17"> <td class="xl70" style="height:17.0pt;border-top:none" align="right" height="17">2001003072</td> <td class="xl68" style="border-top:none">Aguilar, Ivan A.</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:#9C6500;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#FFEB9C;mso-pattern:black none">21</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:#9C0006;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#FFC7CE;mso-pattern:black none">Fuqua</td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:#006100;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#C6EFCE;mso-pattern:black none">43</td> </tr> </tbody></table>

 

[Scott Law]

-- removed inline image ---

 

[texasalynn]

so what from your example means they have detention?

 

[Scott Law]

If their name is there, then they will have detention. I only scan the kids id's who will be getting detention. The scanner pulls in the students ID number the the students name and room numbers are then pulled by vlookup. I have the room numbers color-coded using conditional formatting. The colors correspond to the geographic area on campus. I use a few macros with this sheet to order the kids by room so security can pick them up.

THANK YOU FOR ANY HELP.
Scott