Excel custom data validation does not work when clicking another cell

Question

I have a complex data validation requirement that means the custom validation looks for a TRUE or FALSE in another cell.

This works perfectly if I enter data in the target cell and press <Enter> or <Tab>, but not if I enter data in the target cell and click another cell with the mouse.

The validation cell uses data from the target cell. I suspect that there is a difference in the sequence of calculation and validation depending whether the user switches focus to another cell using the mouse or keyboard.

Steps to reproduce:

Cell A1 contains formula =IF(B1="X",TRUE,FALSE)

Cell B1 has custom data validation =A1

Delete any value from cell B1

Enter "X" in cell B1 and press <Enter> or <Tab>

Success

Delete any value from cell B1

Enter "X" in cell B1 and click cell C1

Data validation returns an error

Trust me, I've tried to write the actual, complex, validation formula into the data validation dialogue, but it will not accept the formula!

Answer 1

Dear Stuart,

Good day! Thank you for posting to Microsoft Community. We are happy to help you. 

Per the description shared, I understand the concern you explained i.e., after entering the value in the cell with Custom Validation, then clicking on the other cell in the worksheet, instead of clicking Enter or Tab will give an error. Based on the steps provided, I performed the same steps and noticed the same behavior as you described.

In this situation, I suggest you report the behavior to the related development via app feedback in the Excel application. Reference: How do I give feedback on Microsoft Office?

At last, I will keep this thread open so Excel MVPs and experts in this community will share their ideas and views about this behavior.

Thanks for your cooperation.

Sincerely,

Mia | Microsoft Community Moderator

***Note: In the event that you're unable to reply to this thread, please ensure that your Email address is verified in the Community Website by clicking on Your Account Name > "My Profile" > "Edit Profile" > Add your Email Address > tick "Receive email notifications" checkbox > click on "Save".***