# Data Validation - Formula Trouble

• Having some trouble with this one.

K1 needs Data Validation for following.

IF I1-H1=0 then allow data entry of whole number into K1. IF I1-H1>0 then allow NO data entry.

I've tried a standard IF statement in a custom Data Validation, but it's not working

• Re: Data Validation - Formula Trouble

Could you attach an example workbook?

I'm not 100% sure that you can do what you want with Data>Validation...

Then again I may be wrong.:)

Boo!:yikes:

• Re: Data Validation - Formula Trouble

Try this :

Allow - Whole Number
Data - Between
Minimum - =IF(H1=I1,-9999,0)
Maximum - =IF(H1=I1,9999,0)

HTH

• Re: Data Validation - Formula Trouble

Quote from norie

Then again I may be wrong.:)

I'm not sure if you can either, but I figured I'd give someone here on the answer. If it can't be done by Data Validation what would the formula be?

## Files

• Re: Data Validation - Formula Trouble

Quote from carlmack

Try this :

Allow - Whole Number
Data - Between
Minimum - =IF(H1=I1,-9999,0)
Maximum - =IF(H1=I1,9999,0)

HTH

Thanks - doesn't work though

When the difference between the Buy and Sell rate is = 0, there should be no data entry allowed into Flat Paid. If the difference is > 0, data entry is allowed into Flat Paid.

• Re: Data Validation - Formula Trouble

I'm a little confused.

Quote

IF I1-H1=0 then allow data entry of whole number into K1.

Quote

When the difference between the Buy and Sell rate is = 0, there should be no data entry allowed ...

Going by your last post I guess you should replace the = with <>.

## Files

• Re: Data Validation - Formula Trouble

Quote from carlmack

I'm a little confused.

OK, let me try to clear this up. Based on the example attached earlier in the thread:

IF (B2-A2) = 0 then ALLOW data entry to C2 (Flat Paid)
IF (B2-A2) > 0 then DISALLOW any data entry to C2 (Flat Paid)

Sell Rate will ALWAYS be larger than Buy Rate.

Basically, if the Buy Rate and Sell Rate are EVER the same, you should be ALLOWED to data enter to C2. If the Sum of B2-A2 is EVER > 0, you should be DISALLOWED to data enter to C2.

• Re: Data Validation - Formula Trouble

Quote from Dave Hawley

Try using a Custom Validation of

=B2-A2=0

Try Dave Hawley solution it works. Here is a sample workbook.

## Files

• Re: Data Validation - Formula Trouble

Quote from Fin Fang Foom

Try Dave Hawley solution it works. Here is a sample workbook.

What about the whole number part of the OP's post?

Boo!:yikes:

• Re: Data Validation - Formula Trouble

Excellent! This works perfect! One other question that just came to mind.

Is there any logic that can be added to address this scenario.

Flat Paid is data entered into C2 when B2-A2 = 0%. If user changes the buy and/or sell rate to be > 0%, is there a formula that will wipe out the Flat Paid cell (C2)?

• Re: Data Validation - Formula Trouble

EgoProwler

In your first post you mentioned that only whole numbers should be allowed.

Is that not applicable now?

Boo!:yikes:

• Re: Data Validation - Formula Trouble

Quote from norie

EgoProwler

In your first post you mentioned that only whole numbers should be allowed.

Is that not applicable now?

Yes, no longer applies. Sorry for the confusion

• Re: Data Validation - Formula Trouble

Quote from EgoProwler

Excellent! This works perfect! One other question that just came to mind.

Is there any logic that can be added to address this scenario.

Flat Paid is data entered into C2 when B2-A2 = 0%. If user changes the buy and/or sell rate to be > 0%, is there a formula that will wipe out the Flat Paid cell (C2)?

OK. After some reading I see that some VBA code will handle what I want to do. However, I have no experience at all with VBA code. I've seen it, understand some of it, but not enough to hammer this snippet out to perform my action.

Based on the most recent attached example the code should handle the event as such:

• When B2-A2 = 0, data entry is allowed into C2 (Flat Paid).

• IF after a value is entered into C2, the difference between B2-A2 > 0, the data in C2 needs to be cleared.

Basically, if the user comes back later and changes the buy/sell rates and that difference is > 0, the data entered into C2 should be wiped out.

Hope this makes sense and thanks for the help. You guys truly are the best!

## Participate now!

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