+ Reply to Thread
Results 1 to 7 of 7

Case Sensitive Data Validation

  1. #1
    Forum Contributor
    Join Date
    10-26-2011
    Location
    Ottawa, ON Canada
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    128

    Case Sensitive Data Validation

    I'm trying to perform data validation from a list of around 300 entries. (Dates in the format of Mar10, Mar11, Mar12, Mar13... etc... I don't want to use a drop down so it will be validating only and giving an error if the entry is incorrect.

    I need the result to be case sensitive so that MAr12 could not be entered for example.

    I see that if you use comma delimited directly in the source this would work, but it seems to have a limit of characters...

    How else would I go about this?

    (I tried searching the form but sam to be getting some Fatal Error message each time..
    Last edited by hambly; 11-21-2011 at 02:08 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Case Sensitive Data Validation

    So is the pattern you want to allow: Upper,lower,lower, and 2 digits?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Contributor
    Join Date
    10-26-2011
    Location
    Ottawa, ON Canada
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    128

    Re: Case Sensitive Data Validation

    If possible, I'd prefer a mix... I also have some different codes that would be acceptable TNR, NS, WDR and QF

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Case Sensitive Data Validation

    So can you actually make a list of acceptable entries somewhere that can be referenced to compare with? If not, we need to establish some sort of allowed pattern...

  5. #5
    Forum Contributor
    Join Date
    10-26-2011
    Location
    Ottawa, ON Canada
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    128

    Re: Case Sensitive Data Validation

    Yes, I can make a list somewhere on the same sheet... The problem I was running into with that method was that the result wasn't case sensitive...

    Example I had the list running from Z1:Z300 and used that as my range as my source, it would still accept mar12, or tar for example..

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Case Sensitive Data Validation

    You can then use Custom data validation formula:

    =EXACT(A1,VLOOKUP(A1,$Z$1:$Z$300,1,0))

    where A1 is the top/left most cell in the validation cells range.

  7. #7
    Forum Contributor
    Join Date
    10-26-2011
    Location
    Ottawa, ON Canada
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    128

    Re: Case Sensitive Data Validation

    How easy is Excel when you know how.... !

    Thanks. Works great. All my time Googling and no-one gave this simple answer...

+ 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