Attendance Sheet Help

[scummins22]

Ok, so I am creating an attendance sheet and I have it looking up the employee's # of points and letting it dictate by vlookup what level write up that employee is on. The catch is, if a employee has a no call no show they receive a point (like an absent) but also a right. Excel gives them the corresponding write up stage based off of the NCNS but when it gets to the next stage of write up it reverts back to the amount of points and does not stack the write ups like it should. I will give more detail below, can someone help me with this.

Amount of Points:
1-None
2-None
3-None
4- 1st Written
5- None
6- 2nd Written
7- None
8- Final Written
9- Termination

1st NCNS - 1st Written (1 point)
2nd NCNS - Final Written (1 point)
3rd NCNS - Termination (1 point)

so for example:

Jim Bob

Absent - 1 point
Absent - 2 points
NCNS- 3 points (1st Written)
Absent - 4 (Should be 2nd Written since employee received 1st written on the NCNS but it is giving me 1st written again)

What can I do to make this work?

 

[oldbrewer]

Absent - 1 point
Absent - 2 points
NCNS- 3 points (1st Written)
Absent - 4 (Should be 2nd Written since employee received 1st written on the NCNS but it is giving me 1st written again)
			data for Jim
			date	WHAT	points	running points	written	running written	issued
			01/04/2018	absent	1	1	0	0	0
			03/05/2018	absent	1	2	0	0	0
			08/05/2018	NCNS	0	2	1	1	written1
			12/05/2018	absent	1	3	1	2	written2
				is this the expected table ?

<colgroup><col span="3"><col><col span="2"><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[scummins22]

Correct,

but the employee will still accrue a point for the NCNS but you are correct.

 

[oldbrewer]

Absent - 1 point

Absent - 2 points

NCNS- 3 points (1st Written)

Absent - 4 (Should be 2nd Written since employee received 1st written on the NCNS but it is giving me 1st written again)

G

H

I

J

data for Jim

date

WHAT

points

running points

written

running written

issued

01/04/2018

absent

1

1

0

0

ROW 17

03/05/2018

absent

1

2

0

0

08/05/2018

NCNS

1

3

1

1

written1

12/05/2018

absent

1

4

1

2

written2

now totally automated

h17

=IF(OR(E17="NCNS",G17>3),1,0)

i17

=H17

i18

=H18+I17

j17

=IF(H17=1,"written"&I17,"")

I assume if 3 points or more = written

<colgroup><col span="3"><col><col span="2"><col><col><col><col><col span="4"></colgroup><tbody>
</tbody>