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

CoolDad

New Member
Joined
Mar 22, 2015
Messages
1
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?
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,003
Office Version
  1. 2019
Platform
  1. Windows
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

Active Member
Joined
Feb 6, 2013
Messages
293
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,003
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,117
Messages
5,546,038
Members
410,721
Latest member
adi772
Top