Mandatory data validation based on value arising in adjacent cell.

[jgopal]

Hello everyone,
Good evening.

I have a data entry worksheet with columns A:L. Only columns in green requires entry by the user.

Is it possible to run a macro when value in column K (Variance) is 1 minute (00:01) or more, then Column L (Delay Reason) has to be updated by the user from a drop down list and only then move to the next row / free cell. Column L is a drop down list with possible reasons why there was a delay.

I saw a lots of macros but nothing meets a similar criteria. Most of them are to not allow save or close the file without filing in the mandatory field. Those will not apply as this file will be in sharepoint and always set to save being online and being collaborated by 3-4 users.


Thanks
Best Regards
J

Client task tracker - ver2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	NO	DATE	DAY	DIVISION	REPORT NAME	CODE	FREQUENCY	RESPONSIBLE	TARGET TIME	COMPLETION TIME	VARIANCE	DELAY REASON
2
3
4
Data

Cell Formulas
Range	Formula
A2:A4	A2	=IF([@DIVISION]="","",ROW()-ROW(Table1[[#Headers],[NO]]))
B2:B4	B2	=IF(A2<>"",IF(B2<>"",B2,NOW()),"")
C2:C4	C2	=IF([@DATE]="","",TEXT([@DATE],"dddd"))
F2:F4	F2	=IFERROR(VLOOKUP([@[REPORT NAME]],WORKING!C:D,2,FALSE),"")
G2:G4	G2	=IFERROR(VLOOKUP([@[REPORT NAME]],WORKING!C:E,3,FALSE),"")
H2:H4	H2	=IFERROR(VLOOKUP([@[REPORT NAME]],WORKING!C:F,4,FALSE),"")
I2:I4	I2	=IFERROR(VLOOKUP([@[REPORT NAME]],WORKING!C:G,5,FALSE),"")
K2:K4	K2	=IFERROR(IF([@[COMPLETION TIME]]-[@[TARGET TIME]]>0,[@[COMPLETION TIME]]-[@[TARGET TIME]], TEXT(ABS([@[COMPLETION TIME]]-[@[TARGET TIME]]),"-hh:mm")),"")

Cells with Data Validation
Cell	Allow	Criteria
D2:D150	List	=WORKING!$J$2:$J$12
E2:E150	List	=OFFSET(WORKING!$L$1,1,MATCH($D2,WORKING!$L$1:$V$1,0)-1,COUNTA(OFFSET(WORKING!L1,1,MATCH(D2,WORKING!$L$1:$V$1,0)-1,10)),1)
L2	List	=WORKING!$J$17:$J$25
L3:L150	List	=WORKING!$J$17:$J$25