+ Reply to Thread
Results 1 to 4 of 4

Data validation does not prevent invalid entries

  1. #1
    Registered User
    Join Date
    09-06-2012
    Location
    Alberton, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    30

    Data validation does not prevent invalid entries

    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.

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Data validation does not prevent invalid entries

    Try these...

    C10: =AND(LEN(B10)>6,D10="",ISNUMBER(C10),C10>0)

    Uncheck: Ignore blank

    D10: =AND(LEN(B10)>6,C10="",ISNUMBER(D10),D10>0)

    Uncheck: Ignore blank
    Last edited by Tony Valko; 09-28-2013 at 08:34 PM.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    09-06-2012
    Location
    Alberton, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Data validation does not prevent invalid entries

    Thank you Tony.

    It works perfectly.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Data validation does not prevent invalid entries

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Data Validation allowing invalid entries
    By Simon1185 in forum Excel General
    Replies: 7
    Last Post: 10-19-2010, 09:15 AM
  2. Replies: 3
    Last Post: 10-18-2010, 11:09 AM
  3. To prevent duplicate entries using Data validation.
    By legendkiller420 in forum Excel General
    Replies: 3
    Last Post: 07-09-2010, 07:47 AM
  4. Data Validation not preventing invalid entries
    By Denise in forum Excel General
    Replies: 2
    Last Post: 04-06-2006, 06:00 PM
  5. [SOLVED] Prevent Duplicate Validation List Entries
    By JK in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-07-2006, 04:20 PM

Tags for this Thread

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