Data Validation Refusing to Work

I have noticed that often when I apply a custom Data Validation to many cells that it will sometimes work and sometimes not, seemingly at random. I am running Excel 2003 with manual calculation mode. The validation sequence is applied to about 50 cells and looks like this:

=(SUM($H$37:$J$53)+SUM($H$63:$J$73)+SUM($H$77:$J$145))<=100

The idea is to not allow any entry in the cells that would put the total over 100 because they are allotting percentages up to 100%.

I've used the exact same formula with normal results in the past, and other times it gave me trouble and then started working. Has anyone heard of this fickle behavior with Data Validation or have any idea what can be done about it?

Thanks!

HI

As far as I can see the problem is that you are using manual calculation and therefore your validation formula will not be evaluated until you manually recalculate the sheet.

Regards

Jeff