Forum Discussion

dan80_30's avatar
dan80_30
Copper Contributor
Nov 27, 2020

DATA VALIDATION

You can help me please, I'm working on an excel sheet and I need that if in cell B1 I write the number 2 then in cell A1 only allow to enter digits between 0 and 1 and that also does not exceed the length of 24,if in B1 I write 16, in A1 only enter data from 0 to 9 and from A to F with length of 6

BI & Data Analysis
excel
Formulas and Functions

5 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Nov 28, 2020

    dan80_30 

     

            A                                                            B

    Formula           

                            Description (Result)

    =DEC2BIN(9, 4)

    Converts decimal 9 to binary with 4 characters (1001)

    =DEC2BIN(-100)

    Converts decimal -100 to binary (1110011100)

    Convert numbers to different number systems

    https://support.microsoft.com/en-us/office/convert-numbers-to-different-number-systems-880eeb52-6e90-4a9d-9e56-acaba6a27560?ui=en-us&rs=en-us&ad=us

     

    I would be happy to know if I could help.

     

    Nikolino

    I know I don't know anything (Socrates)

     

    * Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

     

      • NikolinoDE's avatar
        NikolinoDE
        Platinum Contributor
        Nov 30, 2020

        Willy Lau 

         

         

        I am sure if you searched there would be loads of examples of this, but here is a quick function I knocked up for you:

        Option Explicit
Public Function LongBinary(lngNumber As Long) As String

Dim lngExp As Long
Dim strBinary

If lngNumber < 0 Then Exit Function

Do While 2 ^ lngExp <= lngNumber
strBinary = -((lngNumber And (2 ^ lngExp)) <> 0) & strBinary
lngExp = lngExp + 1
Loop

LongBinary = strBinary

End Function

         

        I would be happy to know if I could help.

         

        Nikolino

        I know I don't know anything (Socrates)

        * Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

         

  • Willy Lau's avatar
    Willy Lau
    Iron Contributor
    Nov 28, 2020

    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)

     

     

     

     

     

Resources