Restrict Data in a Cell using both Decimals AND Letters

    Hi


    I need a specific row of cells to only accept decimals(0 to 24) and a few codes consisting of letters.
    the codes are listed below:
    If I can get the codes in a drop down list it would be great [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 49"]

    [tr]


    [TD="class: xl69, width: 65"]ABS[/TD]

    [/tr]


    [tr]


    [TD="class: xl69"]A[/TD]

    [/tr]


    [tr]


    [TD="class: xl69"]AL[/TD]

    [/tr]


    [tr]


    [TD="class: xl69"]OFF[/TD]

    [/tr]


    [tr]


    [TD="class: xl69"]ML[/TD]

    [/tr]


    [tr]


    [TD="class: xl69"]SL[/TD]

    [/tr]


    [tr]


    [TD="class: xl69"]FRL[/TD]

    [/tr]


    [tr]


    [TD="class: xl69"]SUS[/TD]

    [/tr]


    [tr]


    [TD="class: xl69"]TERM[/TD]

    [/tr]


    [tr]


    [TD="class: xl69"]TL[/TD]

    [/tr]


    [tr]


    [TD="class: xl69"]TRSF[/TD]

    [/tr]


    [tr]


    [TD="class: xl70"]UL[/TD]

    [/tr]


    [/TABLE]
    Can I do this using Data validation or Kutools, if not I was hoping someone could help me with inserting VBA code


    Thank you :)

    Thanks. That would be fine if I was using only whole numbers,
    but since I am using decimals, if I had 11.40 in my list, it wouldn't allow 11.4.
    and my list would be rather long looking like 11.40, 11.41, 11.42, to include every possible option.


    Is there any other ideas you might have?

    Hello,


    Regarding your decimal issue .. to be restricted between 0 and 24 ...


    you could use Data Validation Allow Custom and test following formula :


    Code
    =AND(OR(IF(ISERROR(FIND(".",$A1)),LEN($A1)>0,LEN(MID($A1,FIND(".",$A1)+1,20))<3)),(A1-INT(A1))*100<25)


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Thanks both of you for all your help, its working :)


    I included the codes and all numbers in a list


    and changed my settings of the cell to 2 decimal places (thats why it wasn't working before) silly me


    Thanks Jonathan and Carim

    Glad you could fix your problem ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hello,
    i am new to excel.
    i want to know how to validate data in a cell
    with Decimal or Text (AB) condition


    i want to restrict user to enter any decimal number (0 to 25) or AB in cell.
    Thanks in advance

    Hello,


    It would be safer for you to start your own new thread ... :wink:


    Meanwhile, have you tested the formula suggested in Message # 4 ... :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Thanks for replying and your suggestion Carim
    I have tested the formula given in Massage #4, but after entering formula a Message box appeared "The Formula currently evaluate to en error. Do you want to continue?"

    Re,


    You have noticed that the formula suggested in Message #4 is designed to be placed in cell A1 ...


    Obviously, if you need the same data validation formula in any other cell ... you do need to adjust it accordingly ...:wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login