+ Reply to Thread
Results 1 to 1 of 1

Data validation - works with TAB or ENTER, errors from a Mouse Click

  1. #1
    Registered User
    Join Date
    02-07-2013
    Location
    Telford, England
    MS-Off Ver
    97, XP, 2003 and unfortunately 2010
    Posts
    1

    Data validation - works with TAB or ENTER, errors from a Mouse Click

    Hi All,

    Newbie, here with my 1st post.

    I have data validation on a cell within a sheet that is passed to a lot of customers.
    The validation ensures that the length of the data entered is less than 24 characters and then checks each of these characters to ensure they are valid characters.

    There is then a Sum of these valid characters and if this equals the count of unique characters within the cell this passes, if not then at least one of the characters isn't valid.

    The formula for this is below

    =IF(AND(LEN(A2)>0,LEN(A2)<24),SUM($D$2:$D$156)=(COUNT(1/FREQUENCY(CODE(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1)),ROW(INDIRECT("1:255"))))),FALSE)

    The formula works as expected and had initially passed my testing, however the first person to test this after me encountered an error. If text is entered into the cell and then TAB or ENTER is pressed the valiidation works as expected, if the user keys in the text then clicks on the next cell the validation will return an error message.

    This appears to not allow the cells within column D to calculate the correct values.

    Does anyone know why this works for TAB or ENTER but not with a mouse click, and is there any way around this?


    Hopefully this makes sense

    Chris

    AMENDMENT This happens across Excel 97, XP 2003, 2007 and 2010.
    Last edited by Buzz83; 02-15-2013 at 08:40 AM. Reason: Included Excel versions

+ 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