+ Reply to Thread
Results 1 to 4 of 4

Custom Data Validation That Evaluates for Numbers AND Text

  1. #1
    Registered User
    Join Date
    06-18-2013
    Location
    Greensboro, NC
    MS-Off Ver
    Excel 2010
    Posts
    2

    Custom Data Validation That Evaluates for Numbers AND Text

    I am trying to create a custom data validation rule that would allow only specific values into the cells. These values can be only quarter decimal numbers, whole numbers, or certain text. I used the OR function and it seemed to work. However, I get the #VALUE! error when I mix values and text. I am wondering if the problem is that although I have version 2010 at home, I am working on a laptop that only has version 2003 of Excel.

    Can someone please help me out?

    Here is my formula:
    =OR(C17-0.75=TRUNC(C17), C17-0.5=TRUNC(C17), C17-0.25=TRUNC(C17), C17=INT(C17), C17="A", C17="G", C17="T", C17="D")

    Thanks a million in advance!
    JP

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Custom Data Validation That Evaluates for Numbers AND Text

    Try in data validation

    =IF(ISNUMBER(C17),C17=MROUND(C17,0.25),OR(C17="A",C17="G",C17="T",C17="D"))
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    06-18-2013
    Location
    Greensboro, NC
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Custom Data Validation That Evaluates for Numbers AND Text

    Working with 2003 at the moment is definitely making this difficult! For example, MROUND did not exist
    However, I changed that part and tested it and it looks like this is going to work just fine. THANK YOU so very much for the quick response!
    Many blessings to you.

  4. #4
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Custom Data Validation That Evaluates for Numbers AND Text

    Try this:

    =IFERROR(MOD(A1,0.25)=0,OR(A1="A",A1="G",A1="T",A1="D"))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1