Function my Sum1(Sheet Name as string) as Variant my Sum1=Worksheets(Sheetname). Range("A2") End Function This function will not automatically recalculate if Data!A2 changes: it will only automatically recalculate when Sheetname changes.However, you can change the precision of calculations so that Excel uses the displayed value instead of the stored value when it recalculates formulas.
What this means is that the function will only be flagged as needing evaluation when one or more of the input arguments change (unless the function is volatile).This is the default behavior when you first open a workbook and when you are editing a workbook.However, you can control when and how Excel recalculates formulas.In the tooltip that is shown in the screen shot below, you can see that the shortcut for Calculate Sheet is Shift F9.We tried using the Calculation shortcuts, including Ctrl Alt Shift F9 (that one recalculates everything, and starts your car).
A workbook of mine that worked fine for several years, when using Excel 2003, suddenly refused to update all the formulas, after a switch to Excel 2010. When someone tells you that formulas aren’t calculating, it’s probably because the Calculation setting has been changed to Manual, instead of Automatic.