+ Reply to Thread
Results 1 to 3 of 3

Drop Down List allows small and capital letters.

  1. #1
    Registered User
    Join Date
    04-08-2013
    Location
    London UK
    MS-Off Ver
    Excel 2007
    Posts
    1

    Drop Down List allows small and capital letters.

    Hi, I have used drop down list and data validation before with no problem. Now I created this data validation drop down list for a column with about 300 rows: A8 ; A28 ; A6 ; A10 ; A8 ; Other - Now what happens is: when I just type, instead of using the selction from the list, I can type with a small letters, such as "a28" instead of "A28" and the entry stays as "a28", it doesn't chage to capital letter, despite the validation was done on capitol letters only. This of course jeopardise the objective of the drop list, with people typing some with a, some with A ending up having 11 different entries instead of a maximum of possible 7. I hope it is not too confusing... remember, the validated date in bold above, is the actual data I need my team to imput (they are code for dialysis fluids, I work as a nurse).
    I don't want to end up forcing the entire column to change font to capital letter with the "text" formula applied...
    Is this one of the bug in excel?
    Is anything to do with the fact that "A28" might resemble a cell in column A at row 28?
    Thanks for your help

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,052

    Re: Drop Down List allows small and capital letters.

    Try changing your DV formula/rule to custom and use this...

    =AND(CODE(A1)>64,CODE(A1)<91)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Drop Down List allows small and capital letters.

    Try this...

    Let's assume you want to restrict cell A4 to only accept these EXACT entries:

    A8, A28, A6, A10, Other

    Create this defined name:
    Goto the Formulas tab>Defined Names>Define Name
    Name: Data
    Refers to: ={"A8","A28","A6","A10","Other"}
    OK out

    Set the validation
    Select cell A4
    Goto the Data tab>Data Validation
    Allow: Custom
    Formula: =COUNT(FIND(A4,Data))
    Uncheck Ignore Blank
    OK out
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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