Excel formulas not auto updating
This means that the best way to handle the formula is to use the INDIRECT function, in this manner: The INDIRECT function uses text for a parameter, and since it is text it is not considered a reference to be updated by Excel.Regardless of inserting or deleting columns, the formula will always refer to column B.
For instance, if he has the formula =SUM(B: B) and then he inserts a column to the left of column B, the formula is automatically updated by Excel to =SUM(C: C).
It is not just for addition or AUTOSUM formulas that are not updating automatically.
Step 1: Open the workbook with the formula that is not updating.
Step 2: Click the Formulas tab at the top of the window.
Step 3: Click the Calculation Options button in the Calculation section of the ribbon, then click the Automatic option. If your formulas were already set to update automatically, then there may be a different problem.
You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Stopping a Formula from Updating References.