VBA Code to clear contents of a cell automatically when a trigger value is detected as the lowest value in another column

[JohnGow383]

Hi all. I was hoping I could get some help with some VBA code.
I would like one particular cell (I5) to have contents cleared with one of either of the following two trigger words selected in another column (M4:M53). The trigger words are "NOON in PORT" or "NOON in TRANS".
Also, to make things clear, the trigger words have to be the last value in the mentioned column range. For example, if M25 has "NOON in PORT" selected it will clear I5. The spreadsheet is being copied, renamed with new date and being updated on a daily basis. So if the next day M26 has just "NOON at SEA" I5 will not be cleared and so on. Thanks for any help.

 

[JohnGow383]

John,

I'd be happy to take a look, but it's too hard to follow along without the upload. Can you try uploading again?

Hi, thanks. I appreciate it's very hard to follow and im amazed you were able to follow the first part. I'm on a ship in the Atlantic and the internet isn't great. The mini-sheet will not contain the existing macros. Would it be easier if I emailed the spreadsheet to you, my email address is gpo@gmx.co.uk? If you email me, I'll email you a copy if that's easier? Thanks for everything.

 

[JohnGow383]

13.NOON Jul 13, 2021.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	GEMMATA DAILY FIGURES Copy to Archives Daily	Additional LSDO Used	0.00 MT	Voyage Average RPM / Slip	45.52	8.01%	Previous Day's ROBs Will Update After Clicking Carry Forward	CF LSMGO Total	1902.91	CF LSDO Total	1902.91		Reading Type	Cx	Date / Time
2	DATE / Hrs Since Last Report	Jul 13, 2021	24.0 Hrs	LNG Density / FMF	0.428381	0.88	Daily Water Figures	CF	Made	Used	ROB		Average / Totals
3	Time / Event	12.00 PM	NOON	M/E RPM	38.90 rpm		Distilled FW	292	19	15	296	N/A	FAOP	0	Jun 21, 16:42
4	CF M/T Counter / M/T Counter	40136190	40192210	Avg Shaft Power	1375 kW		Domestic FW	286	0	10	276		NOON	0	Jun 22, 12:00
5	CF FO Meter / LSMGO Counter	43446250	43446250	Avg Shaft Torque	401 kNm		Engine Miles / Obs Miles	229.2	194	Daily Slip	15.4%		NOON	0	Jun 23, 12:00
6	CF Gas Meter / Gas Meter	4043713	4088635	Electrical Load	1129 kW		Total LSMGO Used / ROB	0.23	1902.68	Speed	8.08		NOON	0	Jun 24, 12:00
7	CF № 1 N2 / № 1 N2 Hours	41029	41029	FAOP M/T Counter	38823980		Extra LSMGO Used / ROB	0.23	1902.68	Av Speed Since FAOP			NOON	0	Jun 25, 12:00
8	CF № 2 N2 / № 2 N2 Hours	37793	37812	Tot Eng / Obs Miles	5592.8	5145	FOE / Total Fuel Used	57.13	57.36	10.28 knots			NOON	0	Jun 26, 12:00
9	№ 1 N2 Plant № of Hrs Run	0 Hrs	LSDO Density	LSMGO Used - Meter	0.00 MT		LNG Used MT / cu m	46.50	108.56				NOON	0	Jun 27, 12:00
10	№ 2 N2 Plant № of Hrs Run	19 Hrs	0.8535	Side Tk Temp № 1/2	34°C	34°C	Sludge Total	1.19 cu m					NOON	-1	Jun 28, 12:00
11	CF D/G Meter / D/G 1 MDO mtr	412732	412852	L.S Tk Temp № 1/2	31°C	31°C	Sludge - Used / Gained in 24Hrs	Used	Gained	0.00 cu m			SOP	0	Jun 28, 12:42
12	CF D/G Meter / D/G 2 MDO mtr	322360	322505	Sett Tk Temp № 1/2	37°C	36°C	DUTY ENGINEER	3rd Eng					NOON in PORT	0	Jun 29, 12:00
13	Enter Constants at Start of Voyage After FAOP	FOE Mult	GMF	SW Temp °C (DCS)	28°C	82½°F	SW Temp: 28°C/82½°F						ROP	0	Jun 29, 16:30
14		1.2286	1.0352	ER Temp °F (Local)	113°F	45°C	ER Temp: 113°F/45°C						NOON	0	Jun 30, 12:00
15													NOON	-1	Jul 1, 12:00
16	Voyage Consumptions	At Sea	In Port	Total Combined	Av/Day		Laden GMF Calculator	CTS		Gas Counter			NOON	0	Jul 2, 12:00
17	Total Voyage LNG Cons (MT)	979.23MT	45.08 MT	1024.31 MT	49.12		Depart Loading Port Gauging	136040.295		3894670			NOON	-1	Jul 3, 12:00
18	Total Voyage LNG Cons (m³)	2285.9m³	105.2 m³	2391.11 m³	114.66		Arrival Disch Port Gauging	134616.434		4380052			NOON	0	Jul 4, 12:00
19	Total Voyage HFO Cons	0.00 MT	0.00 MT	0.00 MT	0.0		Calculated GMF	1.2566					NOON	-1	Jul 5, 12:00
20	Total Voyage LSDO Cons	6.00 MT	12.48 MT	18.48 MT	0.89								NOON	0	Jul 6, 12:00
21							Ballast GMF Calculator	CTS		Gas Counter			NOON	0	Jul 7, 12:00
22	Bunker Dips	Suspended M/T Counter		39289450			Depart Disch Port Gauging	6443.002		4402054			NOON	-1	Jul 8, 12:00
23	Waterbook	Resume Passage M/T Ctr		39290660			Arrival Loading Port Gauging	4806.233		5016390			NOON	0	Jul 9, 12:00
24	Veslink Folder						Calculated GMF	1.1413					NOON	0	Jul 10, 12:00
25	In Port Fuel Calculator	Ensure to Leave Blank											NOON	0	Jul 11, 12:00
26	C/O Cargo Documents	Ensure to Leave Blank					LSMGO ROBs - Meter / Tanks	Meter	Soundings	Difference			NOON	0	Jul 12, 12:00
27	C/E EUMRV Docs							1902.68 MT	1908.10 MT	5.42 MT			NOON	0	Jul 13, 12:00
28	Bunker Statements
29	Sludge Management Tool
30
31
32
33
34
35
NOON Figs

Cell Formulas
Range	Formula
E1	E1	=$R$54
F1	F1	=IF($S$54="","",CONCATENATE($S$54,"%"))
B2:B3	B2	=IFERROR(LOOKUP(2,1/($O$3:$O$53<>""),$O$3:$O$53),"N/A")
C2	C2	=LOOKUP(2,1/($L$3:$L$53<>""),$L$3:$L$53)
C3	C3	=IFERROR(IF(OR(LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53)="NOON in PORT",LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53)="NOON in TRANS"),"NOON",LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53)),"N/A")
E3	E3	=IFERROR(IF($C$4="","",IF(OR(LOOKUP(2,1/(M3:M53<>""),M3:M53)="NOON in PORT",LOOKUP(2,1/(M3:M53<>""),M3:M53)="NOON in TRANS"),"N/A",($C$4-$B$4)/(60*$C$2))),"N/A")
H5	H5	=IF(OR($C$4="",C4<B4),"",($C$4-$B$4)*7.577/1852)
H6	H6	=IFERROR(IF($C$1>0,ROUND(IF(OR(C5="",C11="",C12=""),"",(C5-B5)/1000*$F$2+$C$1)+(ROUND(C10*(((C12-B12)/1000)+((C11-B11)/1000)),2)),2),ROUND(IF(OR(C5="",C11="",C12=""),"",(C5-B5)/1000*$F$2)+(ROUND(C10*(((C12-B12)/1000)+((C11-B11)/1000)),2)),2)),"")
I6	I6	=IFERROR(IF(C1<0,IFERROR($I$1-$H$6-$C$1,""),$I$1-$H$6),"")
H7	H7	=IF(OR(C11="",C12=""),"",IF(C1>0,ROUND(C10*(((C12-B12)/1000)+((C11-B11)/1000)),2)+C1,ROUND(C10*(((C12-B12)/1000)+((C11-B11)/1000)),2)))
I7	I7	=IF(C1<0,IFERROR(ROUND($K$1-$H$6-$C$1,2),""),IFERROR(ROUND($K$1-$H$6,2),""))
K3:K4	K3	=H3+I3-J3
K5	K5	=IFERROR(ROUND(IFERROR((H5-I5)/H5,""),3),"")
K6	K6	=IFERROR(IF(ROUND($I$5/$C$2,2)=0,"",(ROUND($I$5/$C$2,2))),"")
E8	E8	=IF($C$4="","",(($C$4-$E$7)-($D$23-$D$22))*7.577/1852)
F8	F8	=SUMIF($Q$4:$Q$53,">0.002")
E9	E9	=IFERROR(ROUND(IF(C5="","",(C5-B5)/1000*$F$2),2),"")
H8	H8	=IFERROR(IF(H9<0,"",ROUND(B14,6)*H9),"")
I8	I8	=IFERROR($H$6+$H$8,"")
J8	J8	=IF($P$2=0,"",CONCATENATE(IFERROR(ROUND((SUMIF($Q$4:$Q$53,">.02"))/$P$54,2),"")," knots"))
H9	H9	=IFERROR(ROUND(IF(C6="","",((C6-B6)/1000)*C14),3),"")
I9	I9	=IFERROR($H$9/$E$2,"")
B9:B10	B9	=IF(C7="","",C7-B7)
F13	F13	=IFERROR(CONCATENATE(IF(AND(IF($E$13="","",($E$13*9/5)+32)-INT(IF($E$13="","",($E$13*9/5)+32))>=0,IF($E$13="","",($E$13*9/5)+32)-INT(IF($E$13="","",($E$13*9/5)+32))<0.3),INT(IF($E$13="","",($E$13*9/5)+32)),IF(AND(IF($E$13="","",($E$13*9/5)+32)-INT(IF($E$13="","",($E$13*9/5)+32))>=0.3,IF($E$13="","",($E$13*9/5)+32)-INT(IF($E$13="","",($E$13*9/5)+32))<0.7),CONCATENATE(INT(IF($E$13="","",($E$13*9/5)+32)),"½"),IF(IF($E$13="","",($E$13*9/5)+32)-INT(IF($E$13="","",($E$13*9/5)+32))>0.7,INT(IF($E$13="","",($E$13*9/5)+32))+1))),"°F"),"")
G13	G13	=IF($E$13="","Enter SW Temp into E13",CONCATENATE("SW Temp: ",$E$13,"°C","/",$F$13))
F14	F14	=IFERROR(CONCATENATE(IF(AND(IF($E$14="","",($E$14-32)*5/9)-INT(IF($E$14="","",($E$14-32)*5/9))>=0,IF($E$14="","",($E$14-32)*5/9)-INT(IF($E$14="","",($E$14-32)*5/9))<0.3),INT(IF($E$14="","",($E$14-32)*5/9)),IF(AND(IF($E$14="","",($E$14-32)*5/9)-INT(IF($E$14="","",($E$14-32)*5/9))>=0.3,IF($E$14="","",($E$14-32)*5/9)-INT(IF($E$14="","",($E$14-32)*5/9))<0.7),CONCATENATE(INT(IF($E$14="","",($E$14-32)*5/9)),"½"),IF(IF($E$14="","",($E$14-32)*5/9)-INT(IF($E$14="","",($E$14-32)*5/9))>0.7,INT(IF($E$14="","",($E$14-32)*5/9))+1))),"°C"),"")
G14	G14	=IF($E$14="","Enter ER Temp into E14",CONCATENATE("ER Temp: ",$E$14,"°F","/",$F$14))
B17	B17	=CONCATENATE(ROUND($AA$2,2),"MT")
C17	C17	=CONCATENATE(ROUND($AB$2,2)," MT")
D17	D17	=Y2
E17	E17	=IFERROR(IF(D17=0,"",D17/$P$2*24),"0 MT/d")
B18	B18	=CONCATENATE(ROUND($AA$1,2),"m³")
C18	C18	=CONCATENATE(ROUND($AB$1,2)," m³")
D18	D18	=CONCATENATE(ROUND($Y$2/$E$2,2)," m³")
E18	E18	=IFERROR(ROUND(($Y$2/$E$2)/P2*24,2),"")
B19	B19	=$AE$2
C19	C19	=$AF$2
D19	D19	=$U$54
E19	E19	=IFERROR(ROUND(D19/P2*24,2),"")
B20	B20	=$AC$2
C20	C20	=$AD$2
D20	D20	=$W$2
E20	E20	=IFERROR(($D$20/P2*24),"")
H19	H19	=IFERROR(ROUND((($H$17-$H$18)*$E$2)/($J$18-$J$17)*1000,4),"")
B22	B22	=IF(COUNTIF($M$3:$M$53,"SOP")>0,"Suspended M/T Counter","Ensure to Leave Blank")
B23	B23	=IF(COUNTIF($M$3:$M$53,"SOP")>0,"Resume Passage M/T Ctr","Ensure to Leave Blank")
H24	H24	=IFERROR(IF(ROUND((($H$22-$H$23)*$E$2)/($J$23-$J$22)*1000,4)<=0,"",ROUND((($H$22-$H$23)*$E$2)/($J$23-$J$22)*1000,4)),"")
B25	B25	=IF(COUNTIF($M$3:$M$53,"SOP2")>0,"SOP2 M/T Counter","Ensure to Leave Blank")
B26	B26	=IF(COUNTIF($M$3:$M$53,"SOP2")>0,"ROP2 Passage M/T Ctr","Ensure to Leave Blank")
H27	H27	=IFERROR(LOOKUP(2,1/(X3:X53<>""),X3:X53),"")
I27	I27	='\\134-dc1\Chief Data\01 Logs\01 Daily Bunkers\[1. GEMMATA DAILY BUNKER.xlsm]Daily Bunkers'!$J$26
J27	J27	=IFERROR(I27-$H$27,"")
B28	B28	=IF((OR(IFERROR(LOOKUP(2,1/(M3:M53<>""),M3:M53),"")="NOON in PORT",IFERROR(LOOKUP(2,1/(M3:M53<>""),M3:M53),"")="NOON in TRANS"))=TRUE,"Do NOT Add M/T Counter in Cells B4 & C4; Leave as EOP or SOP Figure",IF((OR(IFERROR(LOOKUP(2,1/(M3:M53<>""),M3:M53),"")="SOP",IFERROR(LOOKUP(2,1/(M3:M53<>""),M3:M53),"")="ROP"))=TRUE,"Remember to Add Suspended / Resume Passage M/T Counters into D22 & D23",IF((OR(IFERROR(LOOKUP(2,1/(M3:M53<>""),M3:M53),"")="SOP2",IFERROR(LOOKUP(2,1/(M3:M53<>""),M3:M53),"")="ROP2"))=TRUE,"Remember to Add SOP2 / ROP2 Passage M/T Counters into D25 & D26",IF(E7="","Ensure to Add FAOP M/T Counter into Cell E7 & Keep for the Whole Voyage",""))))
L3	L3	=IF(IF($O3="","",IF($N3=0,($O3-$O2)*24,IF($N3=1,($O3-$O2)*24-1,IF($N3=-1,($O3-$O2)*24+1))))>25,"N/A",IF($O3="","",IF($N3=0,($O3-$O2)*24,IF($N3=1,($O3-$O2)*24-1,IF($N3=-1,($O3-$O2)*24+1)))))
L4:L35	L4	=IF($O4="","",IF($N4=0,($O4-$O3)*24,IF($N4=1,($O4-$O3)*24-1,IF($N4=-1,($O4-$O3)*24+1))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I3:J4	Expression	=NOT(I3="")	text	NO
E10:F12	Expression	=NOT(E10="")	text	NO
E6:F6	Expression	=NOT(E6="")	text	NO
E5:F5	Expression	=AND(NOT(E5=""),OR((LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53))="NOON in PORT",(LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53))="NOON in TRANS")=FALSE)	text	NO
E4:F4	Expression	=AND(NOT(E4=""),OR((LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53))="NOON in PORT",(LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53))="NOON in TRANS")=FALSE)	text	NO
E5:F5	Expression	=OR(IFERROR(LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53),"")="NOON in PORT",IFERROR(LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53),"")="NOON in TRANS")=TRUE	text	NO
E4:F4	Expression	=OR(IFERROR(LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53),"")="NOON in PORT",IFERROR(LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53),"")="NOON in TRANS")=TRUE	text	NO
C11:C12	Expression	=NOT(C11="")	text	NO
C7:C8	Expression	=NOT(C7="")	text	NO
C5:C6	Expression	=NOT(C5="")	text	NO
C4	Expression	=AND(NOT(C4=""),OR((LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53))="NOON in PORT",(LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53))="NOON in TRANS")=FALSE)	text	NO
C4	Expression	=OR(IFERROR(LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53),"")="NOON in PORT",IFERROR(LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53),"")="NOON in TRANS")=TRUE	text	NO
C4	Expression	=OR(IFERROR(LOOKUP(2,1/(O3:O53<>""),O3:O53),"")="NOON in PORT",IFERROR(LOOKUP(2,1/(O3:O53<>""),O3:O53),"")="NOON in TRANS")=TRUE	text	NO
B10	Expression	=NOT(B10="")	text	NO
B10	Expression	=B10=""	text	NO
B9	Expression	=NOT(B9="")	text	NO
B9	Expression	=B9=""	text	NO
I8	Expression	=NOT(I8="")	text	NO
I8	Expression	=I8=""	text	NO
H6	Expression	=AND(H6>0,NOT(H6=""))	text	NO
H6	Expression	=H6=0	text	NO
H6	Expression	=H6=""	text	NO
E9:F9	Expression	=AND(E9>0,NOT(E9=""))	text	NO
H7	Expression	=AND(H7>0,NOT(H7=""))	text	NO
H7	Expression	=H7=0	text	NO
H7	Expression	=H7=""	text	NO
I5	Expression	=OR((LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53))="NOON in PORT",(LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53))="NOON in TRANS")=TRUE	text	NO
J8:K9	Expression	=J8=""	text	NO
J8:K9	Expression	=NOT(J8="")	text	NO
I9	Expression	=NOT(I9="")	text	NO
I9	Expression	=I9=""	text	NO
H9	Expression	=NOT(H9="")	text	NO
H9	Expression	=H9=""	text	NO
I6	Expression	=NOT(I6="")	text	NO
I6	Expression	=I6=""	text	NO
I7	Expression	=NOT(I7="")	text	NO
I7	Expression	=I7=""	text	NO
H8	Expression	=NOT(H8="")	text	NO
H8	Expression	=H8=""	text	NO
E9:F9	Expression	=E9=""	text	NO
E9:F9	Expression	=E9=0	text	NO
F1	Expression	=AND(F1>0,NOT(F1=""))	text	NO
F1	Expression	=F1=""	text	NO
E1	Expression	=AND(E1>0,NOT(E1=""))	text	NO
E1	Expression	=E1=""	text	NO
K6	Expression	=K6=""	text	NO
K6	Expression	=K6>0	text	NO
E8	Expression	=E8=""	text	NO
H5	Expression	=H5=""	text	NO
E3:F3	Expression	=E3=""	text	NO
K5	Expression	=K5=""	text	NO
F14	Expression	=F14=""	text	NO
F13	Expression	=F13=""	text	NO
B28:D29	Expression	=$B$28="Ensure to Add FAOP M/T Couter into Cell E7 & Keep for the Whole Voyage"	text	NO
M5:M53	Expression	=M5="ROP2"	text	NO
M5:M53	Expression	=M5="SOP2"	text	NO
M5:M53	Expression	=M5="ROP"	text	NO
M5:M53	Expression	=M5="SOP"	text	NO
M5:M53	Expression	=M5="EOP"	text	NO
M5:M53	Expression	=AND(M6="",NOT(M5=""))	text	NO
M5:M53	Expression	=AND(NOT(M6=""),NOT(M5=""))	text	NO
M4	Expression	=M4="ROP2"	text	NO
M4	Expression	=M4="SOP2"	text	NO
E3:F3	Expression	=AND(NOT(E3=""),OR((LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53))="NOON in PORT",(LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53))="NOON in TRANS")=FALSE)	text	NO
K5	Expression	=NOT(K5="")	text	NO
H5	Expression	=NOT(H5="")	text	NO
E8	Expression	=NOT(E8="")	text	NO
G14	Expression	=G14="Enter ER Temp into E14"	text	NO
G13	Expression	=G13="Enter SW Temp into E13"	text	NO
G13	Expression	=AND($E$13<17,NOT($E$13=""))	text	NO
G13	Expression	=AND($E$13<=26,$E$13>=17)	text	NO
G13	Expression	=$E$13>26	text	NO
F13	Expression	=AND($E$13<17,NOT($E$13=""))	text	NO
F13	Expression	=AND($E$13<=26,$E$13>=17)	text	NO
F13	Expression	=$E$13>26	text	NO
G14	Expression	=AND(CRaw>40,F14="")	text	NO
G14	Expression	=AND(CRaw>40,NOT(F14=""))	text	NO
G14	Expression	=AND(CRaw>40,NOT(F14=""))	text	NO
G14	Expression	=AND(CRaw<=40,CRaw>32)	text	NO
G14	Expression	=AND(CRaw<=32,CRaw>25)	text	NO
G14	Expression	=AND(CRaw<=25,CRaw>20)	text	NO
G14	Expression	=CRaw<=20	text	NO
F14	Expression	=AND(CRaw>40,E14="")	text	NO
F14	Expression	=AND(CRaw>40,NOT(E14=""))	text	NO
E14	Expression	=NOT(E14="")	text	NO
E14	Expression	=NOT(E14="")	text	NO
E13	Expression	=NOT(E13="")	text	NO
H22:K23	Expression	=NOT(H22="")	text	NO
H17:K18	Expression	=NOT(H17="")	text	NO
J11:K11	Expression	=NOT(J11="")	text	NO
H10:K10	Expression	=NOT(H10="")	text	NO
I5	Expression	=NOT(I5="")	text	NO
K1	Expression	=NOT(K1="")	text	NO
I1	Expression	=NOT(I1="")	text	NO
E7:F7	Expression	=NOT(E7="")	text	NO
E2:F2	Expression	=NOT(E2="")	text	NO
B14:C14	Expression	=NOT(B14="")	text	NO
B11:B12	Expression	=NOT(B11="")	text	NO
C10	Expression	=NOT(C10="")	text	NO
B7:B8	Expression	=NOT(B7="")	text	NO
B5:B6	Expression	=NOT(B5="")	text	NO
B4	Expression	=AND(NOT(B4=""),OR((LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53))="NOON in PORT",(LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53))="NOON in TRANS")=FALSE)	text	NO
C1	Expression	=NOT(C1="")	text	NO
B3	Expression	=NOT(B3="N/A")	text	NO
B2	Expression	=NOT(B2="N/A")	text	NO
C2	Expression	=NOT(C2="N/A")	text	NO
C3	Expression	=C3="N/A"	text	NO
B3	Expression	=B3="N/A"	text	NO
B2	Expression	=B2="N/A"	text	NO
C2	Expression	=C2="N/A"	text	NO
D23	Expression	=$B$22="Ensure to Leave Blank"	text	NO
D23	Expression	=$B$22="Suspended M/T Counter"	text	NO
D22	Expression	=$B$22="Ensure to Leave Blank"	text	NO
D22	Expression	=$B$22="Suspended M/T Counter"	text	NO
B28:D29	Expression	=$B$28="Remember to Add SOP2 / ROP2 Passage M/T Counters into D25 & D26"	text	NO
D26	Expression	=$B$25="Ensure to Leave Blank"	text	NO
D26	Expression	=$B$25="SOP2 M/T Counter"	text	NO
D25	Expression	=$B$25="Ensure to Leave Blank"	text	NO
B26:C26	Expression	=$B$25="Ensure to Leave Blank"	text	NO
B25:C25	Expression	=$B$25="Ensure to Leave Blank"	text	NO
D25	Expression	=$B$25="SOP2 M/T Counter"	text	NO
B26:C26	Expression	=$B$25="SOP2 M/T Counter"	text	NO
B25:C25	Expression	=$B$25="SOP2 M/T Counter"	text	NO
B4	Expression	=OR(IFERROR(LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53),"")="NOON in PORT",IFERROR(LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53),"")="NOON in TRANS")=TRUE	text	NO
B4	Expression	=OR(IFERROR(LOOKUP(2,1/(N3:N53<>""),N3:N53),"")="NOON in PORT",IFERROR(LOOKUP(2,1/(N3:N53<>""),N3:N53),"")="NOON in TRANS")=TRUE	text	NO
O16:O17	Expression	=AND(O17="",NOT(O16=""))	text	NO
O16:O17	Expression	=AND(NOT(O17=""),NOT(O16=""))	text	NO
N4:N53	Expression	=AND(NOT(N5=""),NOT(N4=""),N4>0)	text	NO
N4:N53	Expression	=AND(NOT(N5=""),NOT(N4=""),N4<0)	text	NO
E3:F3	Expression	=OR(IFERROR(LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53),"")="NOON in PORT",IFERROR(LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53),"")="NOON in TRANS")=TRUE	text	NO
D5	Expression	=OR(IFERROR(LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53),"")="NOON in PORT",IFERROR(LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53),"")="NOON in TRANS")=TRUE	text	NO
D4	Expression	=OR(IFERROR(LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53),"")="NOON in PORT",IFERROR(LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53),"")="NOON in TRANS")=TRUE	text	NO
D3	Expression	=OR(IFERROR(LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53),"")="NOON in PORT",IFERROR(LOOKUP(2,1/($M$3:$M$53<>""),$M$3:$M$53),"")="NOON in TRANS")=TRUE	text	NO
A4	Expression	=OR(IFERROR(LOOKUP(2,1/(M3:M53<>""),M3:M53),"")="NOON in PORT",IFERROR(LOOKUP(2,1/(M3:M53<>""),M3:M53),"")="NOON in TRANS")=TRUE	text	NO
B23:C23	Expression	=$B$23="Ensure to Leave Blank"	text	NO
B22:C22	Expression	=$B$22="Ensure to Leave Blank"	text	NO
B23:C23	Expression	=$B$23="Resume Passage M/T Ctr"	text	NO
B22:C22	Expression	=$B$22="Suspended M/T Counter"	text	NO
H13:K14	Expression	=LEN(H13)>18	text	NO
J27:K27	Expression	=J27<0	text	NO
J27:K27	Expression	=J27>0	text	NO
N4:N53	Expression	=AND(N5="",NOT(N4=""))	text	NO
N4:N53	Expression	=AND(NOT(N5=""),NOT(N4=""),N4=0)	text	NO
N3	Expression	=AND(N4="",NOT(N3=""))	text	NO
N3	Expression	=AND(NOT(N4=""),NOT(N3=""))	text	NO
O15	Expression	=AND(O16="",NOT(O15=""))	text	NO
O15	Expression	=AND(NOT(O16=""),NOT(O15=""))	text	NO
O3	Expression	=AND(O4="",NOT(O3=""))	text	NO
O3	Expression	=AND(NOT(O4=""),NOT(O3=""))	text	NO
O10	Expression	=AND(O11="",NOT(O10=""))	text	NO
O10	Expression	=AND(NOT(O11=""),NOT(O10=""))	text	NO
O4:O6	Expression	=AND(O5="",NOT(O4=""))	text	NO
O4:O6	Expression	=AND(NOT(O5=""),NOT(O4=""))	text	NO
O7:O9,O11	Expression	=AND(O8="",NOT(O7=""))	text	NO
O7:O9,O11	Expression	=AND(NOT(O8=""),NOT(O7=""))	text	NO
O20	Expression	=AND(O21="",NOT(O20=""))	text	NO
O20	Expression	=AND(NOT(O21=""),NOT(O20=""))	text	NO
M4	Expression	=M4="ROP"	text	NO
M4	Expression	=M4="SOP"	text	NO
M4	Expression	=M4="EOP"	text	NO
M4	Expression	=AND(M5="",NOT(M4=""))	text	NO
M4	Expression	=AND(NOT(M5=""),NOT(M4=""))	text	NO
M3	Expression	=AND(M3>0,NOT(OR(M3="FAOP",M3="EOP")))	text	NO
M3	Expression	=OR(M3="FAOP",M3="EOP")	text	NO
O12:O14,O21:O52,O18:O19	Expression	=AND(O13="",NOT(O12=""))	text	NO
O12:O14,O21:O52,O18:O19	Expression	=AND(NOT(O13=""),NOT(O12=""))	text	NO
C3	Expression	=OR(C3="ROP",C3="ROP2")	text	NO
C3	Expression	=OR(C3="SOP",C3="SOP2")	text	NO
C3	Expression	=C3="FAOP"	text	NO
C3	Expression	=C3="EOP"	text	NO
C3	Expression	=C3="NOON"	text	NO
F14	Expression	=AND(CRaw>40,NOT(E14=""))	text	NO
F14	Expression	=AND(CRaw<=40,CRaw>32)	text	NO
F14	Expression	=AND(CRaw<=32,CRaw>25)	text	NO
F14	Expression	=AND(CRaw<=25,CRaw>20)	text	NO
F14	Expression	=CRaw<=20	text	NO
I11	Expression	=$J$11>0	text	NO
H11	Expression	=$J$11<0	text	NO
A23	Expression	=NOT(#REF!="")	text	NO
B28	Expression	=OR(IFERROR(LOOKUP(2,1/(M3:M53<>""),M3:M53),"")="NOON in PORT",IFERROR(LOOKUP(2,1/(M3:M53<>""),M3:M53),"")="NOON in TRANS")=TRUE	text	NO
B28	Expression	=$B$28="Remember to Add Suspended / Resume Passage M/T Counters into D22 & D23"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C3	List	NOON,EOP,FAOP,SOP,ROP,SOP2,ROP2
N4:N35	List	0, 1, -1, 24, -24
O3:O35	Date	between 1/1/2016 and 1/1/2030
M3	List	FAOP
M4:M53	List	NOON, NOON in PORT, NOON in TRANS, FAOP, EOP, SOP, ROP, SOP2, ROP2
H12:I12	List	2nd Eng,3rd Eng,4th Eng,5th Eng,x-2/E,x-3/E,x-4/E

 

[JohnGow383]

I don't think the sample os very clear and it was too much to do the whole sheet.