Validate cell input character length based on another cell's text input

  • [ATTACH=CONFIG]59282[/ATTACH]


    This is an Excel file used to scan in PC information.
    First the PU_ID Tag is scanned in, then Servicetag, then Make.
    It happens that the wrong input is scanned into the Servicetag cell before the Make is written in.
    I wish to validate that the Servicetag (D2) character length is determined by the Make (E2) input entered.
    Dell servicetags = 7 characters (only alpha/numeric, no special characters allowed)
    HP servicetags = 10 characters (only alpha/numeric, no special characters allowed)
    I need an error msg to pop up stating the Servicetag is incorrect and that the cursor automatically placed back into that error cell for fixing.


    Thank you for any help.

  • Re: Validate cell input character length based on another cell's text input


    Please when posting questions upload a sample workbook - it makes creating a solution easier.
    If I understand you correctly you want the service tag checked once the Make is entered into the sheet. One way is to have a Change Event in the Worksheet - so if the value in Column E is changed then the value in the same row in Column D is checked to :
    1. Make sure it is the correct length
    2. Make sure it only contains alphanumeric characters (no special characters)
    The length can be tested with "len" and the special characters can be tested with a regular expression.
    So in the worksheet you would have something like


    Then in a module (in this case I have named the module "Functions") have the following code (note - you only mentioned Dell and HP - you will need to adapt for additional rules you may have)


    See attached workbook - remember there is code in Worksheet 1 as well as the module called Functions

    Files

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Validate cell input character length based on another cell's text input


    To a Sheet module

  • Re: Validate cell input character length based on another cell's text input




    Thank you, Smuzoen! I will implement and try this.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!