Excel function not updating

To prevent having the date change each time a worksheet using automatic recalculation is opened, try using this keyboard shortcut to enter the current date instead.

A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments.

Last week, we shared a sheet that wasn't calculating correctly.

Specifically, the first formula worked fine, but when we copied it, the formula returned the result of the first formula. In the example sheet, I entered a SUM() function to total each salesperson's commission (row 8) and bonus (row9).

The function, however, is one of Excel's volatile functions which means that it usually updates itself every time a worksheet containing the function is recalculated.

Normally, worksheets recalculate each time they are opened so every day that the worksheet is opened the date will change unless automatic recalculation is turned off.

We tried using the Calculation shortcuts, including Ctrl Alt Shift F9 (that one recalculates everything, and starts your car).

That setting affects all the other workbooks the you open during that session.

In this case, the workbook calculation was set to Automatic on all the machines – that’s the first thing we checked.

If your UDF gets input values from any cells that are not in its argument list then it may not be recalculated, and give the wrong answer.

Mostly you can bypass this problem by doing a Full Calculation (Ctrl-Alt-F9), rather than a recalculation (F9), or by making your UDF volatile, but I strongly recommend that you put all the input cells in the argument list.

Leave a Reply