Results 1 to 4 of 4

Data Validation formula breaks for half of incorrect data

Threaded 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.

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