Importing data to generate email

[mattmcclements]

Hi everyone,

When the offence column is >=3 it should generate an email which is all working, however, the spreadsheet works from importing the employee name, time clocked in and date from another database. The rest of the spreadsheet is then filled out with Vlookup and IF commands. However, the email isn't generating when the information is imported, it will only fill out when the offence number is manually inputted.

Any help would be greatly appreciated!

Lates Tracker.xlsm
	A	B	C	D	E	F		H	I	J	K
1	Employee	Personnel Number	Date	Clock in	Start time	Minutes late		Shift	Responsible	Offence No.	Action
2	#N/A				#N/A	#N/A		#N/A	#N/A	0	#N/A
3	#N/A				#N/A	#N/A		#N/A	#N/A	0	#N/A
4	#N/A				#N/A	#N/A		#N/A	#N/A	0	#N/A
5	#N/A				#N/A	#N/A		#N/A	#N/A	0	#N/A
6	#N/A				#N/A	#N/A		#N/A	#N/A	0	#N/A
7	#N/A				#N/A	#N/A		#N/A	#N/A	0	#N/A
Lates

Cell Formulas
Range	Formula
E2:E7	E2	=VLOOKUP(B2,Master!$C$2:$I$1008,7,FALSE)
F2:F7	F2	=IF(D2>E2,D2-E2,"NOT LATE")
H2:H7	H2	=INDEX(Master!$D$2:$D$1332,MATCH(B2,Master!$C$2:C1007,0))
I2:I7	I2	=IF(H2=$N$2,"Joe Westwell",IF(H2=$N$3,"Aaron Johnson",IF(H2=$N$5,"Gareth Roberts",IF(H2=$N$6,"Steven Jones",IF(H2=$N$4,"Lee Massey",IF(H2=$N$7,"Malcolm Wright",IF(H2=$N$9,"Craig Jones",IF(H2=$N$8,"Lukasz Pawlik",IF(H2=$N$10,"Mike Moffatt",IF(H2=$N$11,"MANAGER",IF(H2=$N$12,"Gary Lee",IF(H2=$N$13,"Gary Lee",IF(H2=$N$14,"Mark Wright")))))))))))))
J2:J7	J2	=COUNTIFS($B$2:B2,B2,$G$2:G2,"LATE")
K2:K7	K2	=IF(G2=$R$1,"NOT LATE",IF(J2>=3,"Investigate",IF(J2=2,"1 Away",IF(J2=1,"First one"))))
A2:A7	A2	=INDEX(Master!$F$2:$F$1332,MATCH(B2,Master!$C$2:C1007,0))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:F3394	Cell Value	between 0.0631944444444444 and 0.999988425925926	text	NO
F2:F3394	Cell Value	<=0.0625	text	NO
F2:F3394	Cell Value	contains "NOT LATE"	text	NO
J2:J3394	Cell Value	=0	text	NO
K2:K3394	Cell Value	contains "NOT LATE"	text	NO
J2:J3394	Cell Value	contains "NOT LATE"	text	NO
I2:I3394	Cell Value	contains "MANAGER"	text	NO
H2:H3394	Cell Value	contains "NA"	text	NO
K2:K3394	Expression	=K2="First one"	text	NO
J2:J3394	Cell Value	=1	text	NO
K2:K3394	Expression	=K2="1 Away"	text	NO
J2:J3394	Cell Value	=2	text	NO
K2:K3394	Expression	=K2="Investigate"	text	NO
C2:C1130,A2:A3394,J2:J3394	Cell Value	between 3 and 1000	text	NO

 

[mattmcclements]

.