+ Reply to Thread
Results 1 to 4 of 4

Data Validation formula breaks for half of incorrect data

Hybrid View

  1. #1
    Registered User
    Join Date
    04-17-2020
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    6

    Data Validation formula breaks for half of incorrect data

    Hi there,
    I'm having an issue using the following formula in my spreadsheet:

    =IF(--ISBLANK(D6)=1,IF(--ISBLANK(J6)=1,1,0),IF(--ISBLANK(J6)=1,1,IF(--OR(J6="P",J6="F")=1,1,0)))

    It's designed to first check that a preceding cell (D6) is blank, and if it is, nothing should be allowed to be typed in said cell (J6). If (D6) is not blank, then (J6) can either be P or F for Pass or Fail. However, it only works half of the time; when (D6) is filled, it will only allow P or F. But when (D6) is not filled, it will allow me to enter anything in (J6) even though the logic statement returns 0 when I do. Am I misunderstanding how the custom data validation performs its check for fail or pass, or is there something else I should be doing, or what? I've attached a sample spread with a basic example of my issue for anyone to try, and hopefully fix.

    Thanks!
    Attached Files Attached Files
    Last edited by ezraNBC; 04-17-2020 at 02:27 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: Data Validation formula breaks for half of incorrect data

    your formula can be shortened to
    =IF(ISBLANK(C4),0,IF(ISBLANK(G4),0,--OR(G4="P",G4="F")))
    but your problem is your validation list setting ("ignore blank")
    Attachment 673073
    Last edited by gue2013; 04-17-2020 at 02:06 PM.

  3. #3
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: Data Validation formula breaks for half of incorrect data

    even shorter
    =AND(NOT(ISBLANK(C4));OR(G6="P";G6="F"))

  4. #4
    Registered User
    Join Date
    04-17-2020
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    6

    Re: Data Validation formula breaks for half of incorrect data

    Thank you gue2103 for solving the issue, and even more for the shorthand advice, so much easier to view lol

+ 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. [SOLVED] Place odd and even number for first half data and second half data
    By shukla.ankur281190 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-12-2018, 04:59 AM
  2. data in user form. need help. want to have half populate and half entry data
    By pecan111 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-22-2016, 12:29 PM
  3. Line breaks in Data Validation
    By BobbyW in forum Excel General
    Replies: 2
    Last Post: 09-28-2016, 05:41 PM
  4. Clear all page breaks, then insert new breaks at change in data
    By Alex0929 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-09-2014, 03:12 PM
  5. [SOLVED] Forumula Results Incorrect when data missing. Wanting to hide the incorrect formula.
    By firemedic6265 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-05-2014, 05:01 PM
  6. Replies: 3
    Last Post: 05-06-2013, 04:03 PM
  7. Replies: 1
    Last Post: 05-06-2013, 03:20 PM

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