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

JohnGow383

New Member
Joined
Jul 6, 2021
Messages
10
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Jul 6, 2021
Messages
10
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

JohnGow383

New Member
Joined
Jul 6, 2021
Messages
10
Office Version
  1. 2013
Platform
  1. Windows
13.NOON Jul 13, 2021.xlsm
ABCDEFGHIJKLMNO
1GEMMATA DAILY FIGURES Copy to Archives DailyAdditional LSDO Used0.00 MTVoyage Average RPM / Slip45.528.01%Previous Day's ROBs Will Update After Clicking Carry ForwardCF LSMGO Total1902.91CF LSDO Total1902.91Reading TypeCx Date / Time
2DATE / Hrs Since Last ReportJul 13, 202124.0 HrsLNG Density / FMF0.4283810.88Daily Water FiguresCFMadeUsedROBAverage / Totals
3Time / Event12.00 PMNOONM/E RPM38.90 rpmDistilled FW2921915296N/AFAOP0Jun 21, 16:42
4CF M/T Counter / M/T Counter4013619040192210Avg Shaft Power 1375 kWDomestic FW286010276 NOON0Jun 22, 12:00
5CF FO Meter / LSMGO Counter4344625043446250Avg Shaft Torque 401 kNmEngine Miles / Obs Miles229.2194Daily Slip15.4% NOON0Jun 23, 12:00
6CF Gas Meter / Gas Meter40437134088635Electrical Load 1129 kWTotal LSMGO Used / ROB0.231902.68Speed8.08 NOON0Jun 24, 12:00
7CF № 1 N2 / № 1 N2 Hours4102941029FAOP M/T Counter38823980Extra LSMGO Used / ROB0.231902.68Av Speed Since FAOP NOON0Jun 25, 12:00
8CF № 2 N2 / № 2 N2 Hours3779337812Tot Eng / Obs Miles5592.85145FOE / Total Fuel Used57.1357.3610.28 knots NOON0Jun 26, 12:00
9№ 1 N2 Plant № of Hrs Run0 HrsLSDO DensityLSMGO Used - Meter0.00 MTLNG Used MT / cu m46.50108.56 NOON0Jun 27, 12:00
10№ 2 N2 Plant № of Hrs Run19 Hrs0.8535Side Tk Temp № 1/234°C34°CSludge Total 1.19 cu m NOON-1Jun 28, 12:00
11CF D/G Meter / D/G 1 MDO mtr412732412852L.S Tk Temp № 1/231°C31°CSludge - Used / Gained in 24Hrs UsedGained0.00 cu m SOP0Jun 28, 12:42
12CF D/G Meter / D/G 2 MDO mtr322360322505Sett Tk Temp № 1/237°C36°CDUTY ENGINEER3rd Eng NOON in PORT0Jun 29, 12:00
13Enter Constants at Start of Voyage After FAOPFOE MultGMFSW Temp °C (DCS) 28°C82½°FSW Temp: 28°C/82½°F ROP0Jun 29, 16:30
141.22861.0352ER Temp °F (Local)113°F45°CER Temp: 113°F/45°C NOON0Jun 30, 12:00
15 NOON-1Jul 1, 12:00
16Voyage ConsumptionsAt SeaIn PortTotal CombinedAv/DayLaden GMF CalculatorCTSGas Counter NOON0Jul 2, 12:00
17Total Voyage LNG Cons (MT)979.23MT45.08 MT1024.31 MT49.12Depart Loading Port Gauging136040.2953894670 NOON-1Jul 3, 12:00
18Total Voyage LNG Cons (m³)2285.9m³105.2 m³2391.11 m³114.66Arrival Disch Port Gauging 134616.4344380052 NOON0Jul 4, 12:00
19Total Voyage HFO Cons0.00 MT0.00 MT0.00 MT0.0Calculated GMF1.2566 NOON-1Jul 5, 12:00
20Total Voyage LSDO Cons6.00 MT12.48 MT18.48 MT0.89 NOON0Jul 6, 12:00
21Ballast GMF CalculatorCTSGas Counter NOON0Jul 7, 12:00
22Bunker DipsSuspended M/T Counter39289450Depart Disch Port Gauging6443.0024402054 NOON-1Jul 8, 12:00
23WaterbookResume Passage M/T Ctr39290660Arrival Loading Port Gauging 4806.2335016390 NOON0Jul 9, 12:00
24Veslink FolderCalculated GMF1.1413 NOON0Jul 10, 12:00
25In Port Fuel CalculatorEnsure to Leave Blank NOON0Jul 11, 12:00
26C/O Cargo DocumentsEnsure to Leave BlankLSMGO ROBs - Meter / TanksMeterSoundingsDifference NOON0Jul 12, 12:00
27C/E EUMRV Docs1902.68 MT1908.10 MT5.42 MT NOON0Jul 13, 12:00
28Bunker Statements  
29Sludge Management Tool 
30 
31 
32 
33 
34 
35 
NOON Figs
Cell Formulas
RangeFormula
E1E1=$R$54
F1F1=IF($S$54="","",CONCATENATE($S$54,"%"))
B2:B3B2=IFERROR(LOOKUP(2,1/($O$3:$O$53<>""),$O$3:$O$53),"N/A")
C2C2=LOOKUP(2,1/($L$3:$L$53<>""),$L$3:$L$53)
C3C3=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")
E3E3=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")
H5H5=IF(OR($C$4="",C4<B4),"",($C$4-$B$4)*7.577/1852)
H6H6=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)),"")
I6I6=IFERROR(IF(C1<0,IFERROR($I$1-$H$6-$C$1,""),$I$1-$H$6),"")
H7H7=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)))
I7I7=IF(C1<0,IFERROR(ROUND($K$1-$H$6-$C$1,2),""),IFERROR(ROUND($K$1-$H$6,2),""))
K3:K4K3=H3+I3-J3
K5K5=IFERROR(ROUND(IFERROR((H5-I5)/H5,""),3),"")
K6K6=IFERROR(IF(ROUND($I$5/$C$2,2)=0,"",(ROUND($I$5/$C$2,2))),"")
E8E8=IF($C$4="","",(($C$4-$E$7)-($D$23-$D$22))*7.577/1852)
F8F8=SUMIF($Q$4:$Q$53,">0.002")
E9E9=IFERROR(ROUND(IF(C5="","",(C5-B5)/1000*$F$2),2),"")
H8H8=IFERROR(IF(H9<0,"",ROUND(B14,6)*H9),"")
I8I8=IFERROR($H$6+$H$8,"")
J8J8=IF($P$2=0,"",CONCATENATE(IFERROR(ROUND((SUMIF($Q$4:$Q$53,">.02"))/$P$54,2),"")," knots"))
H9H9=IFERROR(ROUND(IF(C6="","",((C6-B6)/1000)*C14),3),"")
I9I9=IFERROR($H$9/$E$2,"")
B9:B10B9=IF(C7="","",C7-B7)
F13F13=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"),"")
G13G13=IF($E$13="","Enter SW Temp into E13",CONCATENATE("SW Temp: ",$E$13,"°C","/",$F$13))
F14F14=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"),"")
G14G14=IF($E$14="","Enter ER Temp into E14",CONCATENATE("ER Temp: ",$E$14,"°F","/",$F$14))
B17B17=CONCATENATE(ROUND($AA$2,2),"MT")
C17C17=CONCATENATE(ROUND($AB$2,2)," MT")
D17D17=Y2
E17E17=IFERROR(IF(D17=0,"",D17/$P$2*24),"0 MT/d")
B18B18=CONCATENATE(ROUND($AA$1,2),"m³")
C18C18=CONCATENATE(ROUND($AB$1,2)," m³")
D18D18=CONCATENATE(ROUND($Y$2/$E$2,2)," m³")
E18E18=IFERROR(ROUND(($Y$2/$E$2)/P2*24,2),"")
B19B19=$AE$2
C19C19=$AF$2
D19D19=$U$54
E19E19=IFERROR(ROUND(D19/P2*24,2),"")
B20B20=$AC$2
C20C20=$AD$2
D20D20=$W$2
E20E20=IFERROR(($D$20/P2*24),"")
H19H19=IFERROR(ROUND((($H$17-$H$18)*$E$2)/($J$18-$J$17)*1000,4),"")
B22B22=IF(COUNTIF($M$3:$M$53,"SOP")>0,"Suspended M/T Counter","Ensure to Leave Blank")
B23B23=IF(COUNTIF($M$3:$M$53,"SOP")>0,"Resume Passage M/T Ctr","Ensure to Leave Blank")
H24H24=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)),"")
B25B25=IF(COUNTIF($M$3:$M$53,"SOP2")>0,"SOP2 M/T Counter","Ensure to Leave Blank")
B26B26=IF(COUNTIF($M$3:$M$53,"SOP2")>0,"ROP2 Passage M/T Ctr","Ensure to Leave Blank")
H27H27=IFERROR(LOOKUP(2,1/(X3:X53<>""),X3:X53),"")
I27I27='\\134-dc1\Chief Data\01 Logs\01 Daily Bunkers\[1. GEMMATA DAILY BUNKER.xlsm]Daily Bunkers'!$J$26
J27J27=IFERROR(I27-$H$27,"")
B28B28=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",""))))
L3L3=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:L35L4=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
CellConditionCell FormatStop If True
I3:J4Expression=NOT(I3="")textNO
E10:F12Expression=NOT(E10="")textNO
E6:F6Expression=NOT(E6="")textNO
E5:F5Expression=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)textNO
E4:F4Expression=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)textNO
E5:F5Expression=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")=TRUEtextNO
E4:F4Expression=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")=TRUEtextNO
C11:C12Expression=NOT(C11="")textNO
C7:C8Expression=NOT(C7="")textNO
C5:C6Expression=NOT(C5="")textNO
C4Expression=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)textNO
C4Expression=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")=TRUEtextNO
C4Expression=OR(IFERROR(LOOKUP(2,1/(O3:O53<>""),O3:O53),"")="NOON in PORT",IFERROR(LOOKUP(2,1/(O3:O53<>""),O3:O53),"")="NOON in TRANS")=TRUEtextNO
B10Expression=NOT(B10="")textNO
B10Expression=B10=""textNO
B9Expression=NOT(B9="")textNO
B9Expression=B9=""textNO
I8Expression=NOT(I8="")textNO
I8Expression=I8=""textNO
H6Expression=AND(H6>0,NOT(H6=""))textNO
H6Expression=H6=0textNO
H6Expression=H6=""textNO
E9:F9Expression=AND(E9>0,NOT(E9=""))textNO
H7Expression=AND(H7>0,NOT(H7=""))textNO
H7Expression=H7=0textNO
H7Expression=H7=""textNO
I5Expression=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")=TRUEtextNO
J8:K9Expression=J8=""textNO
J8:K9Expression=NOT(J8="")textNO
I9Expression=NOT(I9="")textNO
I9Expression=I9=""textNO
H9Expression=NOT(H9="")textNO
H9Expression=H9=""textNO
I6Expression=NOT(I6="")textNO
I6Expression=I6=""textNO
I7Expression=NOT(I7="")textNO
I7Expression=I7=""textNO
H8Expression=NOT(H8="")textNO
H8Expression=H8=""textNO
E9:F9Expression=E9=""textNO
E9:F9Expression=E9=0textNO
F1Expression=AND(F1>0,NOT(F1=""))textNO
F1Expression=F1=""textNO
E1Expression=AND(E1>0,NOT(E1=""))textNO
E1Expression=E1=""textNO
K6Expression=K6=""textNO
K6Expression=K6>0textNO
E8Expression=E8=""textNO
H5Expression=H5=""textNO
E3:F3Expression=E3=""textNO
K5Expression=K5=""textNO
F14Expression=F14=""textNO
F13Expression=F13=""textNO
B28:D29Expression=$B$28="Ensure to Add FAOP M/T Couter into Cell E7 & Keep for the Whole Voyage"textNO
M5:M53Expression=M5="ROP2"textNO
M5:M53Expression=M5="SOP2"textNO
M5:M53Expression=M5="ROP"textNO
M5:M53Expression=M5="SOP"textNO
M5:M53Expression=M5="EOP"textNO
M5:M53Expression=AND(M6="",NOT(M5=""))textNO
M5:M53Expression=AND(NOT(M6=""),NOT(M5=""))textNO
M4Expression=M4="ROP2"textNO
M4Expression=M4="SOP2"textNO
E3:F3Expression=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)textNO
K5Expression=NOT(K5="")textNO
H5Expression=NOT(H5="")textNO
E8Expression=NOT(E8="")textNO
G14Expression=G14="Enter ER Temp into E14"textNO
G13Expression=G13="Enter SW Temp into E13"textNO
G13Expression=AND($E$13<17,NOT($E$13=""))textNO
G13Expression=AND($E$13<=26,$E$13>=17)textNO
G13Expression=$E$13>26textNO
F13Expression=AND($E$13<17,NOT($E$13=""))textNO
F13Expression=AND($E$13<=26,$E$13>=17)textNO
F13Expression=$E$13>26textNO
G14Expression=AND(CRaw>40,F14="")textNO
G14Expression=AND(CRaw>40,NOT(F14=""))textNO
G14Expression=AND(CRaw>40,NOT(F14=""))textNO
G14Expression=AND(CRaw<=40,CRaw>32)textNO
G14Expression=AND(CRaw<=32,CRaw>25)textNO
G14Expression=AND(CRaw<=25,CRaw>20)textNO
G14Expression=CRaw<=20textNO
F14Expression=AND(CRaw>40,E14="")textNO
F14Expression=AND(CRaw>40,NOT(E14=""))textNO
E14Expression=NOT(E14="")textNO
E14Expression=NOT(E14="")textNO
E13Expression=NOT(E13="")textNO
H22:K23Expression=NOT(H22="")textNO
H17:K18Expression=NOT(H17="")textNO
J11:K11Expression=NOT(J11="")textNO
H10:K10Expression=NOT(H10="")textNO
I5Expression=NOT(I5="")textNO
K1Expression=NOT(K1="")textNO
I1Expression=NOT(I1="")textNO
E7:F7Expression=NOT(E7="")textNO
E2:F2Expression=NOT(E2="")textNO
B14:C14Expression=NOT(B14="")textNO
B11:B12Expression=NOT(B11="")textNO
C10Expression=NOT(C10="")textNO
B7:B8Expression=NOT(B7="")textNO
B5:B6Expression=NOT(B5="")textNO
B4Expression=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)textNO
C1Expression=NOT(C1="")textNO
B3Expression=NOT(B3="N/A")textNO
B2Expression=NOT(B2="N/A")textNO
C2Expression=NOT(C2="N/A")textNO
C3Expression=C3="N/A"textNO
B3Expression=B3="N/A"textNO
B2Expression=B2="N/A"textNO
C2Expression=C2="N/A"textNO
D23Expression=$B$22="Ensure to Leave Blank"textNO
D23Expression=$B$22="Suspended M/T Counter"textNO
D22Expression=$B$22="Ensure to Leave Blank"textNO
D22Expression=$B$22="Suspended M/T Counter"textNO
B28:D29Expression=$B$28="Remember to Add SOP2 / ROP2 Passage M/T Counters into D25 & D26"textNO
D26Expression=$B$25="Ensure to Leave Blank"textNO
D26Expression=$B$25="SOP2 M/T Counter"textNO
D25Expression=$B$25="Ensure to Leave Blank"textNO
B26:C26Expression=$B$25="Ensure to Leave Blank"textNO
B25:C25Expression=$B$25="Ensure to Leave Blank"textNO
D25Expression=$B$25="SOP2 M/T Counter"textNO
B26:C26Expression=$B$25="SOP2 M/T Counter"textNO
B25:C25Expression=$B$25="SOP2 M/T Counter"textNO
B4Expression=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")=TRUEtextNO
B4Expression=OR(IFERROR(LOOKUP(2,1/(N3:N53<>""),N3:N53),"")="NOON in PORT",IFERROR(LOOKUP(2,1/(N3:N53<>""),N3:N53),"")="NOON in TRANS")=TRUEtextNO
O16:O17Expression=AND(O17="",NOT(O16=""))textNO
O16:O17Expression=AND(NOT(O17=""),NOT(O16=""))textNO
N4:N53Expression=AND(NOT(N5=""),NOT(N4=""),N4>0)textNO
N4:N53Expression=AND(NOT(N5=""),NOT(N4=""),N4<0)textNO
E3:F3Expression=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")=TRUEtextNO
D5Expression=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")=TRUEtextNO
D4Expression=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")=TRUEtextNO
D3Expression=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")=TRUEtextNO
A4Expression=OR(IFERROR(LOOKUP(2,1/(M3:M53<>""),M3:M53),"")="NOON in PORT",IFERROR(LOOKUP(2,1/(M3:M53<>""),M3:M53),"")="NOON in TRANS")=TRUEtextNO
B23:C23Expression=$B$23="Ensure to Leave Blank"textNO
B22:C22Expression=$B$22="Ensure to Leave Blank"textNO
B23:C23Expression=$B$23="Resume Passage M/T Ctr"textNO
B22:C22Expression=$B$22="Suspended M/T Counter"textNO
H13:K14Expression=LEN(H13)>18textNO
J27:K27Expression=J27<0textNO
J27:K27Expression=J27>0textNO
N4:N53Expression=AND(N5="",NOT(N4=""))textNO
N4:N53Expression=AND(NOT(N5=""),NOT(N4=""),N4=0)textNO
N3Expression=AND(N4="",NOT(N3=""))textNO
N3Expression=AND(NOT(N4=""),NOT(N3=""))textNO
O15Expression=AND(O16="",NOT(O15=""))textNO
O15Expression=AND(NOT(O16=""),NOT(O15=""))textNO
O3Expression=AND(O4="",NOT(O3=""))textNO
O3Expression=AND(NOT(O4=""),NOT(O3=""))textNO
O10Expression=AND(O11="",NOT(O10=""))textNO
O10Expression=AND(NOT(O11=""),NOT(O10=""))textNO
O4:O6Expression=AND(O5="",NOT(O4=""))textNO
O4:O6Expression=AND(NOT(O5=""),NOT(O4=""))textNO
O7:O9,O11Expression=AND(O8="",NOT(O7=""))textNO
O7:O9,O11Expression=AND(NOT(O8=""),NOT(O7=""))textNO
O20Expression=AND(O21="",NOT(O20=""))textNO
O20Expression=AND(NOT(O21=""),NOT(O20=""))textNO
M4Expression=M4="ROP"textNO
M4Expression=M4="SOP"textNO
M4Expression=M4="EOP"textNO
M4Expression=AND(M5="",NOT(M4=""))textNO
M4Expression=AND(NOT(M5=""),NOT(M4=""))textNO
M3Expression=AND(M3>0,NOT(OR(M3="FAOP",M3="EOP")))textNO
M3Expression=OR(M3="FAOP",M3="EOP")textNO
O12:O14,O21:O52,O18:O19Expression=AND(O13="",NOT(O12=""))textNO
O12:O14,O21:O52,O18:O19Expression=AND(NOT(O13=""),NOT(O12=""))textNO
C3Expression=OR(C3="ROP",C3="ROP2")textNO
C3Expression=OR(C3="SOP",C3="SOP2")textNO
C3Expression=C3="FAOP"textNO
C3Expression=C3="EOP"textNO
C3Expression=C3="NOON"textNO
F14Expression=AND(CRaw>40,NOT(E14=""))textNO
F14Expression=AND(CRaw<=40,CRaw>32)textNO
F14Expression=AND(CRaw<=32,CRaw>25)textNO
F14Expression=AND(CRaw<=25,CRaw>20)textNO
F14Expression=CRaw<=20textNO
I11Expression=$J$11>0textNO
H11Expression=$J$11<0textNO
A23Expression=NOT(#REF!="")textNO
B28Expression=OR(IFERROR(LOOKUP(2,1/(M3:M53<>""),M3:M53),"")="NOON in PORT",IFERROR(LOOKUP(2,1/(M3:M53<>""),M3:M53),"")="NOON in TRANS")=TRUEtextNO
B28Expression=$B$28="Remember to Add Suspended / Resume Passage M/T Counters into D22 & D23"textNO
Cells with Data Validation
CellAllowCriteria
C3ListNOON,EOP,FAOP,SOP,ROP,SOP2,ROP2
N4:N35List0, 1, -1, 24, -24
O3:O35Datebetween 1/1/2016 and 1/1/2030
M3ListFAOP
M4:M53ListNOON, NOON in PORT, NOON in TRANS, FAOP, EOP, SOP, ROP, SOP2, ROP2
H12:I12List2nd Eng,3rd Eng,4th Eng,5th Eng,x-2/E,x-3/E,x-4/E
 

JohnGow383

New Member
Joined
Jul 6, 2021
Messages
10
Office Version
  1. 2013
Platform
  1. Windows
I don't think the sample os very clear and it was too much to do the whole sheet.
 

Forum statistics

Threads
1,141,068
Messages
5,704,087
Members
421,326
Latest member
pfaustino

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top