excel alert msg & if statment

[kelvin_9]

greeting to ALL:

i have 2 setting issues need your help with my setup below:

Q1) is there any alert msg can be applied on either column E&F or column K&L, if cell have been selected?
example: E62 have been selected, i want K62 black in color and comes with a easy msg "wrong selected"

Q2) how can i amend my formula in R70, using a "/" instead of false

thanks for your guidance

NEW 1.3_v2.xlsx
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
62	√			20		1		√			19		1	a to b
63		√				1	√
64		√				1	√
65
66
67
68
69
70						1								FALSE
71						1
72						1
73
74
75
76
77
176 - Disney Explorers Lodge

Cell Formulas
Range	Formula
Q62,Q70	Q62	=IFERROR(IF(O62=20,0,IF(O62>20,(20-(O62)),20-((O62)))),"")
R62,R70	R62	=IF(Q62=0,"/",IF(AND(O62<20,Q62>0),"a to b",IF(AND(O62>20,Q62<0),"b to a")))
H62,H70	H62	=IF(S54="","",IF(S54="no",O54,IF(OR(R54="/"),20,IF(AND(S54="yes",R54="a to b"),(O54+Q54),IF(AND(S54="yes",R54="b to a"),(O54+Q54))))))
O62,O70	O62	=IFERROR(H62-COUNTIF(K62:K69,"><")+COUNTIF(L62:L69,"<>"),"")
J62:J77	J62	=IF(ISBLANK(D62),"",1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
R54:R85	Cell Value	contains "a to b"	text	NO
R54:R85	Cell Value	contains "b to a"	text	NO
E54:F85	Expression	=K54="√"	text	NO
K54:L85	Expression	=E54="√"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E62:F77	List	√
K62:L77	List	√

 

[SanjayGMusafir]

how can i amend my formula in R70, using a "/" instead of false

Try this -

=IFS(Q62=0,"/",AND(O62<20,Q62>0),"a to b",AND(O62>20,Q62<0),"b to a",True,"/")

 

[kelvin_9]

Try this -

=IFS(Q62=0,"/",AND(O62<20,Q62>0),"a to b",AND(O62>20,Q62<0),"b to a",True,"/")

thank you very much for your reply, SanjayGMusafir

it seems work prefect and no error till now

thanks for your guidance

 

[SanjayGMusafir]

Glad to help you