Data validation: Conditional response setup not flagging errors

[kdorian]

I am trying to set up data validation for a report, but two columns aren't flagging blank cells they it should.

The "Blank" list is the title and one blank cell below it. The AcceptReason has four selections below the title, and the RejectReason has ten. Column I has data validation for length.


1st condition, in column K: =IF($I2="",Blank,Status)
What it's supposed to do: If the cell in column I is blank, then "Blank" list, else from "Status" list. ("Accept" or "Reject")

2nd condition, in column L:
=IF($I2="",Blank,IF($K2="A",AcceptReason,RejectReason))
What it's supposed to do: If the cell in column I is blank, then "Blank" list, else response from either "Accept" list or "Reject" list, based on what is in column K


But if I put something in a row in column I, and leave K & L blank, it doesn't flag K or L as errors. What am I doing wrong?

 

[6StringJazzer]

That's not how data validation works. It will not rescan those cells every time something else changes and indicate an error. The only time the data validation rule is checked is when the entry in the cell containing the rule changes. Am I understanding your question correctly?

When you put something in I2, do you get the expected dropdown list in K and L?

 

[kdorian]

My apology, I phrased that poorly. If I enter the data as described and then have invalid data circled, it does not show as a circled cell.

I do get the expected dropdowns in K and L. It's just not flagging any cells as invalid when I select "Circle Invalid Cells," which is what I need it to do. The data I will be checking isn't going to be caught on entry, as it is copied and pasted from another workbook that I can't change the validation on. (That workbook doesn't flag multiple types of errors and does flag hundreds of valid cells, making it almost completely useless.)