none
Macro or Formula for check data

    Question

  • Hello , i need put in validation data a formula or vba a code for check the next :

    In the sheet (“Codes”) put the code for example in column A “asdf” and in the column B put the 125

    In the second sheet (“Length”) put next values below:

    · 
    Codes
    Length
    asdf 100
    asdf 25
    asdf 25


    I need check that values in sheet “Length” is greater than 0 and less than or equal to the total length for that code defined in sheet “Codes”

    thank you , im newbie 
    Thursday, January 31, 2019 3:05 PM

Answers

  • Welcome to the forum.

    You may rephrase your question if my assumption below is not correct.

    If you are trying to validate the entries in column B on Length Sheet to make sure that for the code entered in column A, length is greater than 0 and less than or equal to the length entered for that code on Sheet Codes, please make a validation rule for column B on Length Sheet using the formula given below...

    =AND(B2>0,B2<=INDEX(Codes!$B:$B,MATCH($A2,Codes!$A:$A,0)))

    The above formula assumes that the entries in column B on Length Sheet starts from Row2.


    Subodh Tiwari (Neeraj) sktneer

    • Marked as answer by LoganTRK Thursday, January 31, 2019 3:58 PM
    Thursday, January 31, 2019 3:30 PM

All replies

  • Welcome to the forum.

    You may rephrase your question if my assumption below is not correct.

    If you are trying to validate the entries in column B on Length Sheet to make sure that for the code entered in column A, length is greater than 0 and less than or equal to the length entered for that code on Sheet Codes, please make a validation rule for column B on Length Sheet using the formula given below...

    =AND(B2>0,B2<=INDEX(Codes!$B:$B,MATCH($A2,Codes!$A:$A,0)))

    The above formula assumes that the entries in column B on Length Sheet starts from Row2.


    Subodh Tiwari (Neeraj) sktneer

    • Marked as answer by LoganTRK Thursday, January 31, 2019 3:58 PM
    Thursday, January 31, 2019 3:30 PM
  • Thank you so much!!! Its work :) 
    Thursday, January 31, 2019 3:59 PM
  • You're welcome! Glad it worked as desired.

    Thanks for the feedback.


    Subodh Tiwari (Neeraj) sktneer

    Thursday, January 31, 2019 4:43 PM