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

1. ## 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.``

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

Maybe:

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

3. ## 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. ## 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. ## 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. ## 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. ## 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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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