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.
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.
