In all the years I have used excel I have never once encountered this problem so hopefully this helps anyone else in the situation. I have been used to Excel automatically updating its values (say in a sum() column) whenever I change values above, but somehow today it stopped working. fortunately my better half happens to be an Excel nerd, so with her help I worked out what it was.
If you find that the values in Excel are not updating automatically it is probably because you have not got the (succinctly named) “calculation options” switched to automatic. Apparently back in the day you were required to press F9 to do calculations at any stage, and indeed this still works, but if you want a more permanent solution in Excel 2007 at least goto:
Formulas > Calculation > Calculation options
Then check the “Automatic” selection. This should rectify the problem immediately.