How to use Data Validation to limit calculated cell in Excel?
As we know, the Data Validation can limit values entered, but have you ever tried to limit the calculated results by using Data Validation function? For instance, here are three cells, A1, A2 and A3, what I need is to limit the sum of these three cells not greater than 10, if the value entered in the three cells lead to the sum greater than 10, a warning dialog pops out and stop entering.
Use data validation to limit calculated cell
Use data validation to limit calculated cell
1. Select the calculated cells you use and click Data > Data Validation > Data Validation.
2. In the Data Validation dialog, click Custom from the Allow drop-down list, and then type this formula to =SUM($A$1:$A$3)<=10 to the Formula textbox. See screenshot:
3. Then go to the Error Alert tab, and type the error warning you want in the Error message.
4. Click OK, and the warning dialog will pop out if the values entered in the cells lead to the sum greater than 10.
Relative Articles:
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!
