+ Reply to Thread
Results 1 to 7 of 7

Validate two fields when one field you need a formula to validate

  1. #1
    Registered User
    Join Date
    06-11-2010
    Location
    Windsor, Canada
    MS-Off Ver
    Excel 2007
    Posts
    55

    Question Validate two fields when one field you need a formula to validate

    I have two columns that I need to do a validation on.

    Column O is the Cost of the Item with freight

    Column S is the cost of Self Assessed HST 8%

    The client keys in each of the following columns. I attached a small pictorial of my spreadsheet to give you a better picture of what I am talking about.

    Column O is the Cost of the Item with freight
    Column P is Freight
    Column Q is GST
    Column R is HST 8%
    Column S is the cost of Self Assessed HST 8%
    Column T is PST

    Whenever the person keys in the value in column S I have to make sure that the amount is less than or equal to 8% of the Cost with freight (Column O). I identified these two columns in my spreadsheet pictorial. Currently I am doing this coding with data validation code. But the client can enter positive or negative values so how can I change my data validation logic to also include negative values. I was going to code this in the WORKSHEET_CHANGE in my macro but I understood that if you are not validating against another keyed input field then you had to do your logic with the DATA VALIDATION. Here is what I currently have for code. I am currently looking on the internet for a possible idea. Right now I am looking at defining a complicated if statement to take into account both my negative an positive conditionals. Any advice would be greatly appreciated. I had changed the coding to use absolute values but then how do I make sure if the cost of the item with freight is negative then I make sure they key in a negative amount. Here is my current data validation formula code.

    Please Login or Register  to view this content.
    Attached Images Attached Images
    Last edited by cmwilbur; 06-29-2010 at 09:31 AM.
    cmwilbur

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Validate two fields when one field you need a formula to validate

    Maybe:

    = AND($S1>0,$S1 <= ROUND($O1 * 0.08, 2))
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    06-11-2010
    Location
    Windsor, Canada
    MS-Off Ver
    Excel 2007
    Posts
    55

    Re: Validate two fields when one field you need a formula to validate

    What do I do when column S has a value less than zero (a negative value because they are reversing a previous entry). I have to accomodate negative entries as well. I will try you recommendation.

  4. #4
    Registered User
    Join Date
    06-11-2010
    Location
    Windsor, Canada
    MS-Off Ver
    Excel 2007
    Posts
    55

    Re: Validate two fields when one field you need a formula to validate

    Ideally this is the logic I would need but it does not work

    = AND($S1>0,$S1 <= ROUND($O1 * 0.08, 2)) OR AND($S1<0,$S1 >= ROUND($O1 * 0.08, 2))

    The section after the OR takes care of if the amount is a negative value.

    Any ideas? The part highlighted in blue works fine.

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Validate two fields when one field you need a formula to validate

    Try:

    =OR(AND($S1>0,$S1<=ROUND($O1*0.08,2)),AND($S1<0,$S1>=ROUND($O1*0.08,2)))

    or maybe you want:

    = AND(ABS($S1)>0,ABS($S1) <= ROUND($O1 * 0.08, 2)) so that both the S value and O values coincide..

  6. #6
    Registered User
    Join Date
    06-11-2010
    Location
    Windsor, Canada
    MS-Off Ver
    Excel 2007
    Posts
    55

    Re: Validate two fields when one field you need a formula to validate

    The absolute value code causes a problem with the negative values but the following code you recommended works fine. It takes care of both logic when the amount is coded as negative or positive.

    =OR(AND($S1>0,$S1<=ROUND($O1*0.08,2)),AND($S1<0,$S1>=ROUND($O1*0.08,2)))

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Validate two fields when one field you need a formula to validate

    Yes, it did depend on how you wanted S1 and O1 to compare.

+ 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