How to restrict value entry/input in a cell based on another cell value in Excel?
Supposing you need to restrict value entry in cells based on another cell value, for example, cells in range B1:E10 are allowed to enter data if the value of cell A1 is Yes, but restrict data entry if another value existing in cell A1. How to achieve it? Please try the method in this article.
You can achieve it with the Data Validation feature. Please do as follows.
1. Select range B1:E10, then click Data > Data Validation. See screenshot:
2. In the Data Validation dialog box, go to the Settings tab, select Custom in the Allow drop-down list, enter formula =$A$1="yes" into the Formula box, then uncheck the Ignore blank box. And finally click the OK button. See screenshot:
Note: You can change the formula based on your needs.
From now on, when value of cell A1 is “Yes”, the specified range cells are allowed to input data. See screenshot:
But if cell A1 is other value or blank, cells of specified range are restricted value entry. After entering data in any cell of range B1:E10, you will get a prompt box as below screenshot shown.
- How to restrict access to a certain worksheet in Excel?
- How to restrict to paste values only (prevent formatting) in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!