+ Reply to Thread
Results 1 to 8 of 8

Limit Validation Rule to List and If Entry is an Alpha Character Ensure it is Upper Case

  1. #1
    Registered User
    Join Date
    03-27-2014
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    37

    Limit Validation Rule to List and If Entry is an Alpha Character Ensure it is Upper Case

    Hello

    I found a post where ChemistB advised on a very cool custom validation formulae to limit to a list as well as ensure proper case. I've used the same but for upper case with some of my cells:
    Please Login or Register  to view this content.
    But now I have a scenario where the valid list could have numbers or alpha characters though all should be saved as text. The above would work if the list was entirely text but if the entry is a number the user is forced to enter a single apostrophe first so the number is saved as text. How can I get around this?

    Thanks very much.

  2. #2
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,187

    Re: Limit Validation Rule to List and If Entry is an Alpha Character Ensure it is Upper Ca

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by tony h; 09-20-2015 at 06:37 PM.


    click on the * Add Reputation if this was useful or entertaining.

  3. #3
    Registered User
    Join Date
    03-27-2014
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Limit Validation Rule to List and If Entry is an Alpha Character Ensure it is Upper Ca

    Hi Tony H

    Thanks for weighing in. I have other code in the cells so that if data changes from the original value in B14 (compared to K14) it will turn the cell yellow. Using your validation, if I only enter the field of 1 (which is valid) it shows as being different from B14 even if B14 is also 1. So I have to enter '1 (apostrophe 1) to make it text to show it matches. That means that I still can't enter in without the apostrophe.

    Also, strangely, the template for these worksheets has this column as text but when I copy data into it, then it reverts to being "general" which I suspect is the real problem behind having to enter with the apostrophe.

    So thanks Tony, your method will probably work if I can fix the other issue which I'll post about separately.
    Last edited by shellp; 09-20-2015 at 06:49 PM.

  4. #4
    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
    52,917

    Re: Limit Validation Rule to List and If Entry is an Alpha Character Ensure it is Upper Ca

    I shortened Chemist's formula to this...
    =AND(MATCH(K14,Values!$F2:$F6,0),EXACT(K14,UPPER(K14)))
    and it works for text or numeric or numeric/text
    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

  5. #5
    Registered User
    Join Date
    03-27-2014
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Limit Validation Rule to List and If Entry is an Alpha Character Ensure it is Upper Ca

    Thanks so much but it doesn't work. However, I know the reason it doesn't work is because this column is formatted as general so any entry of the numbers must be with an apostrophe to enter as text and that is what I'm trying to get away from. So I think your code and Chemist's and Tony H's will work as I want once I fix this problem. I posted in another thread how the issue is that the field should be text but exporting from Access is changing that format even though I selected "keep format".

  6. #6
    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
    52,917

    Re: Limit Validation Rule to List and If Entry is an Alpha Character Ensure it is Upper Ca

    The format of the cell will have no affect on the rule.

    Thanks so much but it doesn't work.
    It worked for me.

    DV rules (like Conditional Formatting rules) work on TRUE or FALSE. You can struture a formula for entry directly into a WS that will simulate the DV rule. Below are 3 tables that use the exact same rule to text for alphanumeric, then alpha and then numeric (all that changes is the match range). In each case, you would look for a TRUE if the rule is met, FALSE if it is not met.

    1. Alphanumeric...
    A
    B
    C
    D
    2
    aa5aa
    FALSE
    aa5aa
    3
    aa5AA
    FALSE
    bb10bb
    4
    AA5AA
    TRUE
    cc15cc
    5
    dd20dd
    6
    ee25ee

    B2=AND(MATCH(A2,$D$2:$D$6,0),EXACT(A2,UPPER(A2)))
    copied down

    Alpha...
    A
    B
    C
    D
    8
    aa
    FALSE
    aa
    9
    AA
    TRUE
    bb
    10
    cc
    11
    dd
    12
    ee

    B8=AND(MATCH(A8,$D$8:$D$12,0),EXACT(A8,UPPER(A8)))

    Numeric...
    A
    B
    C
    D
    14
    5
    TRUE
    5
    15
    6
    #N/A
    10
    16
    15
    17
    20
    18
    25

    B14=AND(MATCH(A14,$D$14:$D$18,0),EXACT(A14,UPPER(A14)))

    You can see from this that no matter what data I enter, if teh conditions are met, the rule works

  7. #7
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,187

    Re: Limit Validation Rule to List and If Entry is an Alpha Character Ensure it is Upper Ca

    Can you attach the sheet.

  8. #8
    Registered User
    Join Date
    03-27-2014
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Limit Validation Rule to List and If Entry is an Alpha Character Ensure it is Upper Ca

    Hi Tony

    Sorry I haven't been clear but your formulae and approach should be correct.....the problem is that my data isn't in the format I wanted it to be in i.e. it converts to general when exporting from Access so that is why I have to enter '1 to enter as text which is what I'm trying to avoid.

    So, right now I'm working on a macro to change all the formats on the raw worksheet page BEFORE I parse it out to the worksheets that I'm using validation on. So I may still need assistance but for now I have to do the other first.

    Thanks so much for your help.

+ 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. Replies: 2
    Last Post: 04-18-2014, 04:12 PM
  2. Replies: 3
    Last Post: 02-06-2014, 03:44 AM
  3. Upper case only, in data validation?
    By proepert in forum Excel General
    Replies: 2
    Last Post: 12-08-2010, 11:37 PM
  4. Upper-case for the Second Character
    By longbow007 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-21-2009, 12:51 AM
  5. Limit Character Entry on Cells and Font Question
    By ARPRINCE in forum Excel General
    Replies: 2
    Last Post: 02-28-2008, 04:57 PM
  6. [SOLVED] Determine whether a character is Upper or Lower case
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-02-2005, 05:50 PM
  7. Replies: 14
    Last Post: 08-25-2005, 10:05 PM

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