Jools wrote:
If I enter new information onto a worksheet, the item is not included in the
totals box unless I leave the worksheet and then come back to it. I've tried
resetting the Tools - Options - Calculations to automatic - but it doesn't
help

You are probably experiencing long re-calculation times.

A couple of speed-up suggestions

[1] Try to replace the formulas using OFFSET and INDIRECT with ones that
do not invoke these and other volatile functions.

[2] Try to restrict the array formulas, inclusing the formulas with
SumProduct to relevant subranges or replace them with formulas which
invoke SumIf (or CountIf).

[3] Try to replace lookup formulas with the match-type = 0 (or FALSE)
with lookup formulas with the match-type = 1.

See http://www.decisionmodels.com for more tips.