I see, it doesn't update the values. Even asking the sheet to recalculate doesn't update the values.
Ctrl+Alt+F9 on the keyboard seems to update the sheet (whole workbook I think).
Run this to update the sheet:
Code:
Sub ViewPrjByMth()
With Sheets("PrjByMth").Range("B2:BI15")
.FormulaR1C1 = "=(IF(ISBLANK(RC1),0,(IF(ISBLANK(R1C),0,prjbymonth(RC1,R1C)))))"
.Value = .Value 'if you disable this line by adding an apostrophe before it, the sheet will have all your formulae back in.
End With
End Sub
The problem lies with the function's arguments on the sheet not referring directly to the data on the
FYnnnn sheets; Excel knows to recalculate a formula when any cell in its arguments changes, which is not the case here.
You could, instead of running this code, leave your formulae as they are in the sheets but you'd need to add
Application Volatile directly after the
Dim statement in the
PrjByMonth function's code. This brings in its own set of problems (lots of time recalculating the sheeet unnecessarily which you could control by setting
Calculation to manual).