I am trying to set up a Journal entry form for our accounts department. Part of the form consists of:
Column B - Account number and description
Column C - Debit Amount
Column D - Credit Amount
I want to use Data Validation on Columns C and D. The rules for each row are:
1) NO entry in either columns C or D if Column B is blank.
2) On positive amounts are allowed.
3) Can not have an amount in both columns C and D.
I have the following custom validations set:
Column B - Limited to List
Column C - =AND(LEN(B10)>6,C10>0,D10=0)
Column D - =AND(LEN(B10)>6,C10=0,D10>0)
1st Problem
My problem is that the Data validation prevents only some of the invalid entries, i.e.:
- Validation works in column C.
- Validation for column D prevents entry of positive amount if column C already entered, but does not prevent entry of negative amount. Also entry is allowed even if column B is blank.
2nd Problem
I am running a macro which unlocks all the cells the user can enter data into, including the above mentioned columns B, C and D. However, Data validation prevents All entries in columns C and D, even valid entries.
I would prefer to use Data validation, as I want to prevent the user from entering invalid data, and not test for validity afterwards via vba.
I would appreciate any help to sort this out.
Thank you.
Bookmarks