Forum Discussion
DATA VALIDATION
dan80_30 Understood your needs, but the data validation may need to be written in Macro.
HEX value (FFFFFF) is 16777215 in decimal number. This is too big to create a table mapping for a List in data validation. Also, if you change the value of B1, data validation of A1 will not prompt you. In this way, I will use Conditional Formatting.
You can define a Name, e.g. CorrectContent16
* select A1 cell for easy setup
=AND(LEN(A1)<=6,LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9",""),"A",""),"B",""),"C",""),"D",""),"E",""),"F",""))=0)
The substitute function is to replace the correct characters, and so, the wrong characters will be remained in the string. So, if the len is not 0(zero), it means the string contains other characters.
define another Name, e.g. CorrectContent02
=AND(LEN(A1)<=24,LEN(SUBSTITUTE(SUBSTITUTE(A1,"0",""),"1",""))=0)
Setup a Conditional Formatting rule on A1 to make it with background red color and white font when
=AND(OR(B1=2,B1=16), NOT(OR(AND(B1=2, CorrectContent02),AND(B1=16, CorrectContent16))))
where,
=AND(OR(B1=2,B1=16), XXXXXXXXX)
is to make it works only when B1 = 2 or B1 =16
# Updated on 1-Dec, after Nikolino's suggestion
In Data Validation dialog box, select Settings tab, select Custom in Allow dropdown list, and enter the formula below
=IF(B1=2,AND(LEN(A1)<=24,LEN(SUBSTITUTE(SUBSTITUTE(A1,"0",""),"1",""))=0),IF(B1=16,IFERROR(AND(LEN(A1)<=6,HEX2DEC(A1)<=HEX2DEC("FFFFFF")),FALSE),TRUE))
* if B1 is other value, ignore (always true)