How to put data validation using formula for the case mentioned in description?

**meetvekaria** · 09-21-2017, 09:00 AM

Version Info:
MS Office Version used: 2013
Compatibility of the solution required for versions: 2007 and above.

Case:
Help required to put data validation on the whole column using formula. (Unable to form a formula)

Details:
There are three columns (say) A, B and C. Need to put data validation in column B & C so that following business rules are validated:
1. All three columns should have positive decimal amount only.
2. Amount under A and B should be same.
3. If both A and B are zero, then only amount under C can be entered. In other words, amount under C should be more than zero if A=B=0, and if A and B has amounts, C should be zero.

Note:
1. Formula cannot be kept in the excel directly to put the value entered in A directly in B, as then formula have to be put in all the columns.
2. Table functionality cannot be used in order to support older versions.

**kev_** · 09-21-2017, 10:22 AM

All three columns should have positive decimal amount only

Does this mean all values are <1 ?

**meetvekaria** · 09-21-2017, 10:27 AM

Originally Posted by kev_

Does this mean all values are <1 ?

No. I meant positive decimal values, that is >1. Numbers can be whole or in decimal. Natural numbers.

**kev_** · 09-21-2017, 10:39 AM

do you mean
> 1 OR > 0

**kev_** · 09-21-2017, 10:53 AM

EDIT : formula amended and file replaced (error in original submission)

I have assumed you mean > 0
(easy to amend the formula if you meant >1 )
The formula below are Data Validation formula

in A1
=IF(B1>0,A1=B1,A1>=0)

In B1
=AND(B1>=0,B1=A1)

In C1
=IF(B1=0,C1>0,C1=0)

test in attached workbook