Dissallow entry in cells if value of cell goes above value

[dubaifly]

Hi guys,

Can anyone help me out with some code...

1. I've got a row of 12 cells.
2. Values in each cell could be any number from 1 - 20.
3. The sum of the row must not go over 44
4. Need the user therefore, to be alerted when the value of the sum goes over and if it does, clear the contents of the cell they just input into which took the total over.

Can this be done?

Thanks!

 

[Andrew Poulsom]

You can use Data Validation with a Custom formula:

http://www.contextures.com/xlDataVal07.html

If your cells are in the range A1:A12, select them, and with A1 as the active cell the formula would be:

=AND(A1>=1,A1<=20,SUM(A$1:A$12)<=44)

 

[dubaifly]

Thanks for you quick reply....just trying to implement and will let you know how it works out! Thanks....

 

[dubaifly]

Andrew, thank you soo much it has worked....and I actually understand it. Wow, will be using that again in the future definitely!

 

[dubaifly]

Just a quick follow up question that is probably not possible in excels built-in validation feature but wonderd if I could put something like ' =SUM(C14:N14) ' in the actual error message to say tell the user the current value of the row?

Cheers.

 

[Andrew Poulsom]

No, you can't put a formula in the error message box.

 

[dubaifly]

No, you can't put a formula in the error message box.

OK...thanks.