Ok so this will be a fairly complicated question. I apologize in advance for the length and complexity...but hopefully some of you experts will take it as a challenge. I'm fairly certain this will require some macro coding, but if at all possible, I would like to be able to accomplish this via formula exclusively. <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 path="m@4@5l@4@11@9@11@9@5xe" o:spt="75" coordsize="21600,21600" stroked="f" filled="f" o
referrelative="t"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock v:ext="edit" aspectratio="t"></o:lock></v:shapetype><v:shape id=_x0000_i1025 style="WIDTH: 12pt; HEIGHT: 12pt" type="#_x0000_t75" alt="0"><v:imagedata o:href="http://www.mrexcel.com/forum/images/smilies/biggrin.gif" src="file:///C:\DOCUME~1\gdeel\LOCALS~1\Temp\msohtml1\01\clip_image001.gif"></v:imagedata></v:shape><o></o>
<o></o>
So to provide a bit of background context for the question, I am working with an employee attendance program for my department at work. The program provides a an open field for each day of the year, and the manager (me) selects one value for the corresponding day. So for example, I have a column listing all of the dates of the year (365 rows). The column next to it contains a validation-based drop down box in each cell with which to select a set of given codes. There are twelve codes in total, ranging from "W" for worked, "O" for off, "CI" for called in, etc. and the list goes on. Each day at the conclusion of the shift, I fill in these values for each of my 100+ employees. Now for codes that indicate a performance issue (call-ins, lates, no call no shows, etc.), there are point values as assigned by our company handbook. So for a call-in, an associate gets 2 points, a late gets 1 point, etc. (The twelve codes get complicated with points but there are fixed point values to respective codes). I have these code, descriptions and point values in a separate table on the sheet, in the follow format (using "/" as cell division)<o></o>
<o></o>
W / Worked Normal Shift / 0 points<o></o>
O / Day Off / 0 points<o></o>
CI / Called In / 2 points<o></o>
L / Late / 1 point<o></o>
<o></o>
etc.<o></o>
<o></o>
The reason for the point system (just to add context) is for disciplinary action. So for example, if an employee reaches 5 points, they are written up, 10 points is suspension, and so on.<o></o>
<o></o>
So the question is....how do I get Excel to identify all of the dates where an incident involving points (call-ins, lates, etc.) is involved and then list them chronologically with a total at the bottom. The list would be viewed and printed for purposes of showing an employee their infractions for the year. Ideally it would populate in this format...<o></o>
<o></o>
13 Aug 2009 / Called In / 2 points<o></o>
15 Aug 2009 / Late / 1 point<o></o>
Total / 3 points<o></o>
<o> </o>
The list would obviously exclude days where no infraction occurred.<o></o>
<o></o>
This list could populate in another sheet if necessary...I just need it to show all of the incidents whereby some point value (not including zeros) occurred. I'm sure this will be a lot of “IF” and “LOOKUP” formulas, but I don’t know how to write it.<o></o>
<o></o>
There are two more curveballs in this process. There is a free-form area in the system where I can add my own incidents and/or points as necessary if I need to. For example, if an employee received a write up, I would write in a designated field...<o></o>
<o></o>
14 Aug 09 / Write-up administered / 0 points<o></o>
<o></o>
The second curveball is even worse (and may not be possible to add). According to my company's handbook, if an employee goes 30 consecutive days without incurring any points (or in other words, has no infractions for 30 days straight), one point will be deducted from the total points withstanding. So I have an enormously long formula already configured that derives the number of occurrences of 30 consecutive days with out infraction, but I don’t know how I would get Excel to identify the date of the 30th day where 30 consecutive days of no infractions was reached (and one point was deducted). If possible, I would like for these events to be added into the chronological order of the list so that the points add up correctly on the list. For example a complete list might look something like this…<o></o>
<o> </o>
Employee: Eddie Excel<o></o>
<o></o>
13 Aug 2009 / Called In / 2 points<o></o>
15 Aug 2009 / Late / 1 point<o></o>
17 Aug 2009 / Called In / 2 points<o></o>
17 Aug 2009 / Write-up 5 points / --------<o></o>
16 Sep 2009 / 30 days no infractions / -1 point<o></o>
Total 4 points<o></o>
<o></o>
This list could populate in a fixed area on another sheet, or if possible, Excel could be programmed to add rows as the events occur. That would probably be the best way as a list has the potential of getting very long in the course of an entire year.<o></o>
<o></o>
Again, my apologies for the insanely long post, but I'm hoping there is a much more simple solution to this whole mess. Thanks in advance to any and all for your help! <v:shape id=_x0000_i1026 style="WIDTH: 11.25pt; HEIGHT: 11.25pt" type="#_x0000_t75" alt="0"><v:imagedata o:href="http://www.mrexcel.com/forum/images/smilies/icon_smile.gif" src="file:///C:\DOCUME~1\gdeel\LOCALS~1\Temp\msohtml1\01\clip_image002.gif"></v:imagedata></v:shape><o></o>
referrelative="t"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock v:ext="edit" aspectratio="t"></o:lock></v:shapetype><v:shape id=_x0000_i1025 style="WIDTH: 12pt; HEIGHT: 12pt" type="#_x0000_t75" alt="0"><v:imagedata o:href="http://www.mrexcel.com/forum/images/smilies/biggrin.gif" src="file:///C:\DOCUME~1\gdeel\LOCALS~1\Temp\msohtml1\01\clip_image001.gif"></v:imagedata></v:shape><o></o><o
></o>So to provide a bit of background context for the question, I am working with an employee attendance program for my department at work. The program provides a an open field for each day of the year, and the manager (me) selects one value for the corresponding day. So for example, I have a column listing all of the dates of the year (365 rows). The column next to it contains a validation-based drop down box in each cell with which to select a set of given codes. There are twelve codes in total, ranging from "W" for worked, "O" for off, "CI" for called in, etc. and the list goes on. Each day at the conclusion of the shift, I fill in these values for each of my 100+ employees. Now for codes that indicate a performance issue (call-ins, lates, no call no shows, etc.), there are point values as assigned by our company handbook. So for a call-in, an associate gets 2 points, a late gets 1 point, etc. (The twelve codes get complicated with points but there are fixed point values to respective codes). I have these code, descriptions and point values in a separate table on the sheet, in the follow format (using "/" as cell division)<o
></o><o
></o>W / Worked Normal Shift / 0 points<o
></o>O / Day Off / 0 points<o
></o>CI / Called In / 2 points<o
></o>L / Late / 1 point<o
></o><o
></o>etc.<o
></o><o
></o>The reason for the point system (just to add context) is for disciplinary action. So for example, if an employee reaches 5 points, they are written up, 10 points is suspension, and so on.<o
></o><o
></o>So the question is....how do I get Excel to identify all of the dates where an incident involving points (call-ins, lates, etc.) is involved and then list them chronologically with a total at the bottom. The list would be viewed and printed for purposes of showing an employee their infractions for the year. Ideally it would populate in this format...<o
></o><o
></o>13 Aug 2009 / Called In / 2 points<o
></o>15 Aug 2009 / Late / 1 point<o
></o>Total / 3 points<o
></o><o
> </o>The list would obviously exclude days where no infraction occurred.<o
></o><o
></o>This list could populate in another sheet if necessary...I just need it to show all of the incidents whereby some point value (not including zeros) occurred. I'm sure this will be a lot of “IF” and “LOOKUP” formulas, but I don’t know how to write it.<o
></o><o
></o>There are two more curveballs in this process. There is a free-form area in the system where I can add my own incidents and/or points as necessary if I need to. For example, if an employee received a write up, I would write in a designated field...<o
></o><o
></o>14 Aug 09 / Write-up administered / 0 points<o
></o><o
></o>The second curveball is even worse (and may not be possible to add). According to my company's handbook, if an employee goes 30 consecutive days without incurring any points (or in other words, has no infractions for 30 days straight), one point will be deducted from the total points withstanding. So I have an enormously long formula already configured that derives the number of occurrences of 30 consecutive days with out infraction, but I don’t know how I would get Excel to identify the date of the 30th day where 30 consecutive days of no infractions was reached (and one point was deducted). If possible, I would like for these events to be added into the chronological order of the list so that the points add up correctly on the list. For example a complete list might look something like this…<o
></o><o
> </o>Employee: Eddie Excel<o
></o><o
></o>13 Aug 2009 / Called In / 2 points<o
></o>15 Aug 2009 / Late / 1 point<o
></o>17 Aug 2009 / Called In / 2 points<o
></o>17 Aug 2009 / Write-up 5 points / --------<o
></o>16 Sep 2009 / 30 days no infractions / -1 point<o
></o>Total 4 points<o
></o><o
></o>This list could populate in a fixed area on another sheet, or if possible, Excel could be programmed to add rows as the events occur. That would probably be the best way as a list has the potential of getting very long in the course of an entire year.<o
></o><o
></o>Again, my apologies for the insanely long post, but I'm hoping there is a much more simple solution to this whole mess. Thanks in advance to any and all for your help! <v:shape id=_x0000_i1026 style="WIDTH: 11.25pt; HEIGHT: 11.25pt" type="#_x0000_t75" alt="0"><v:imagedata o:href="http://www.mrexcel.com/forum/images/smilies/icon_smile.gif" src="file:///C:\DOCUME~1\gdeel\LOCALS~1\Temp\msohtml1\01\clip_image002.gif"></v:imagedata></v:shape><o
></o>
