+ Reply to Thread
Results 1 to 4 of 4

Data Validation Formula Is Not Working

  1. #1
    Registered User
    Join Date
    08-03-2015
    Location
    US
    MS-Off Ver
    2010
    Posts
    12

    Data Validation Formula Is Not Working

    I need some help with a Data Validation formula that is not working.

    My Setting for the validation criteria in D1:D10 are:
    Allow: Custom
    Ignore blank: Unchecked
    Formula: =AND($A1=1),OR(AND($B1<>"",$D1<=0),AND($C1<>"",$D1>=0)))

    My formula needs to check:
    (1) If A1 equals 1
    (2) Then check B1, if itís NOT blank
    (3) Only allow a negative (or zero) number in D1
    (4) If B1 is blank; check C1, if itís NOT blank
    (5) Only allow a positive (or zero) number in D1

    Currently, my formula is returning an error that the value in D1 does not match restrictions for the cell. Example: A1=1, C1=25, entering a 0 or any positive number in D1 returns a validation error.

    Iím sure Iím missing the obvious and I would appreciate help getting my formula to work.

    Thank you

  2. #2
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    2013
    Posts
    3,888

    Re: Data Validation Formula Is Not Working

    That's not a valid formula.
    try
    =AND($A1=1,OR(AND($B1<>"",$D1<=0),AND($C1<>"",$D1>=0)))

  3. #3
    Registered User
    Join Date
    08-03-2015
    Location
    US
    MS-Off Ver
    2010
    Posts
    12

    Re: Data Validation Formula Is Not Working

    Fluff13,

    Thank you for your help.

  4. #4
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    2013
    Posts
    3,888

    Re: Data Validation Formula Is Not Working

    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)

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