200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
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.
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.
=(A1="Yes")