Data entered in data validation list is not on the list ==>a formula

[CoolDad]

I am trying to eliminate errant entries in a column that is using a dropdown for a yes/no only entry.

for example the list is in B1:B10.
1. If a user enters "Yes" from the dropdown in B1, "No" in B2, then in B3 enters the formula "=B1", B3 now shows "Yes"
2. Now if the user deletes the entry in B1, B3 now shows "0" which is not allowed per the list of only {Yes, No}

The value in B1:B10 is used in conditional statements in other cells and the calculations are wrong now or the just blows up

So the questions are:
How do I not allow a formula to be entered in the 1st place?
Then how do i keep a non 'validated' value from populating as in 2. above?

 

[jasonb75]

Welcome to MrExcel

I don't think that you will be able to prevent the errors and keep the dropdown (or at least not without using vba), using the formula below as a custom validation rule will prevent the user from entering a formula into any of the validated cells, but they will have to type their choices rather than selecting from a dropdown.

=AND(NOT(ISFORMULA(B1)),OR(B1="Yes",B1="No"))

It would still be possible to bypass the validation using copy and paste but that goes for any rule, not just this one.

 

[sparky2205]

Is it just B1 and B2 that have data validation?
If this is the case then the scenario you have described will be possible i.e. a formula can be entered in B3 since therfe is nothing to prevent it.
If B1:B10 all have the same data validation i.e. Yes,No then you won't be able to enter a formula in B3.
This sounds like too simple an answer. What am I missing?

 

[jasonb75]

If B1:B10 all have the same data validation i.e. Yes,No then you won't be able to enter a formula in B3.

You would be able to enter a formula as long as it evaluates to a result that meets the validation list / criteria.
It is only validated at the point when the formula is first entered, any changes made to the data that the formula is looking at will not be validated, which is the problem that the OP is having.

To the best of my knowledge, my earlier suggestion is the only non vba fix for this.