+ Reply to Thread
Results 1 to 5 of 5

Data Validation - Case Sensitive Issues

  1. #1
    Registered User
    Join Date
    08-25-2015
    Location
    Melbourne, Australia
    MS-Off Ver
    365
    Posts
    17

    Data Validation - Case Sensitive Issues

    Hi everyone,

    I'm having some issues making drop-down lists case sensitive.
    I am really hoping it is possible as some external software requires an identical match.

    Example:

    Drop-down list option = 'Clothing'
    However, 'clothing' is still accepted if typed in and it doesn't produce an error message.

    I understand that I can go into - Data Validation --> Source --> and then type in the options for it to be case sensitive.
    EG: Clothing, Food, Gifts, etc.

    However, this will not work in my situation as it has a 255 character limit and I need at least 470 characters to include all of my options.

    I have included a sample workbook - The column 'Category' (D3:D22) is where I would like the drop-down list with the options from the 'Lists' sheet.

    Any help/suggestions would be greatly appreciated.

    Thanks,

    Nick
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,350

    Re: Data Validation - Case Sensitive Issues

    Name List!A2:A9 with "List"
    Datavalidation/list in cell D3:
    Allow: List
    Source:=List

    Now the dropdown list works. But it can not check for case sensitive issue.

    But, using conditional formatting to highlight itself by red colour, or even black colour.

    =NOT(OR(EXACT(D3,List)))

    pick the format colour.
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,085

    Re: Data Validation - Case Sensitive Issues

    Pl see file.
    I have given Worksheet_Change event for the sheet ClientExpense_DataEntry. When the first letter is low case it will be automatically corrected.
    Code:
    Please Login or Register  to view this content.
    To paste the code
    Right click on Sheet tab --> view code
    VB window opens.
    Paste the code
    Close the window.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,473

    Re: Data Validation - Case Sensitive Issues

    If external software requires exact matches, don't use the range with the data validation drop-down lists to feed the external software. Use another worksheet to shadow your entry worksheet, and use vlookup formulas like D2: =VLOOKUP(ClientExpense_DataEntry!D2,Lists!$A$2:$A$29,1,0). That'd ensure whatever may have been entered in ClientExpense_DataEntry!D2, what's appear in the worksheet with these formulas would be the corresponding values from the Lists worksheet.

  5. #5
    Registered User
    Join Date
    08-25-2015
    Location
    Melbourne, Australia
    MS-Off Ver
    365
    Posts
    17

    Re: Data Validation - Case Sensitive Issues

    Thank you everyone for your prompt responses.

    They've all been great ideas and all of them are excellent options for me to use to solve this issue.

    I really appreciate your time and assistance

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Case Sensitive
    By drew.parker in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-30-2016, 11:32 AM
  2. How to get case sensitive validation from list in a table?
    By dk9000 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-20-2015, 07:40 PM
  3. Data Validation List Issues/Sorting Issues
    By taylorbe in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-12-2014, 09:53 AM
  4. [SOLVED] SUMPRODUCT Or SUMIF - In case of Case sensitive???
    By lifeisaspreadsheet in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-28-2012, 07:57 AM
  5. Case Sensitive Data Validation
    By hambly in forum Excel General
    Replies: 6
    Last Post: 11-21-2011, 01:52 PM
  6. case sensitive validation
    By FRIEL in forum Excel General
    Replies: 4
    Last Post: 03-20-2008, 05:57 AM
  7. Case Sensitive Data Validation
    By blatham in forum Excel General
    Replies: 1
    Last Post: 12-12-2006, 12:07 PM

Tags for this Thread

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