Use Data Validation to Limit or Prevent Data Entry


Is it possible to use Data Validation to prevent (or limit) data entry into a cell based on another cell's value? 

Example.  If A1 is "Yes", then B1 is not available to enter any data; however, if A1 is "No" B1 is available for data entry. But only positive numbers would be allowed B1.

Thank you.



Selected Answer

Yes. That is possible.

On the Data Validation dialog box's Settings tab select Custom from the Allow drop-down. In the Formula field enter a formula like =A1="Yes". This will permit entry in the validated cell only if the formula evaluates to TRUE.

The doubled-up equal sign (also used in CF formulas) has had me confounded for a long time. The logic is very simple, however. If the statement following the equal sign evaluates to TRUE the entry is to be permitted. In it's simplest form the formula =True would permit everything, whereas omission of the equal sign would result in the word "True" not being understood. A more complicated formula, like =SUM(A10:B11)>20, would also comprise a statement which can be true or false but without an equal sign in it. Therefore the rule of thumb is that a leading equal sign is always required whereas further equal signs within the statement that follows are incidental to the statement. To clarify this logical relationship one might write the statement between parentheses.


Answer the Question

You must create an account to use the forum. Create an Account or Login