+ 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
    17

    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 Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    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
    17

    Re: Data Validation Formula Is Not Working

    Fluff13,

    Thank you for your help.

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    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)

Similar Threads

  1. Data Validation Formula not working
    By rich.allan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-18-2018, 07:00 AM
  2. [SOLVED] Ghost Formula in Data Validation [After Closed File Not Working]
    By Jhon Mustofa in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 12-18-2017, 06:25 PM
  3. Multi-select from data validation isn't working with auto-assigning data validation
    By iPenguin in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-11-2017, 12:37 PM
  4. Replies: 3
    Last Post: 07-29-2017, 04:32 AM
  5. Data Validation not working,for excel validation Please Help!!!
    By angelmama in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-09-2014, 10:06 AM
  6. data validation not working if formula triggered?
    By wilcoremmers in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-25-2014, 07:09 PM
  7. validation rules not working when someone copy paste data on validation cell
    By jthakrar in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-17-2010, 03:36 AM

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