Cell not updating?

I have a cell formula, as follows.

=IF(LEFT(Product,4)="U_FP",INDEX((colPolicyNumber,colChargeType),MATCH(IF(ISERROR(VALUE(PolicyNu mber)),PolicyNumber,VALUE(PolicyNumber)),colPolicyNumber,0),1,2),"NONE")

When I open my spreadsheet, all cells update except for this one – it gives a #VALUE! error. I am confident that the formula is correct – it just doesn’t update. The only way I can get it to update is hit F2 to edit the cell, and hit return, without editing. Then it updates with the correct value. How can I get it to automatically update? There is a lot of macro code behind the spreadsheet, so maybe I can do it in there.

Excel has a great tool to debug formulas - step-by-step evaluation. You find it in Tools->Formula Auditing->Evaluate Formula. Using that, it should be easy to see which sub-expression the problem arises from. It's possible that the Evaluator doesn't produce the same result as you see in the cell, I suppose, but it's definitely worth trying.

If that doesn't help, you should next check what each of the custom-terms in the formula is - a named range, a worksheet function, whatever. With that information, we should be able to sort it out, I hope.

Yes, this pinned down the source of the error, thanks for that!

The named range colChargeType is named at the start of calculation, and can be set to a column in one of four different worksheets; if I changed a value in a cell which affected the cell returning the #VALUE!, I would get the error because the named range had not been updated. When I then edited that cell, and hit return, I would get the correct value, because the code behind that sheet would reset the named range.