Macro to recalculate function

Good morning,

I have a function that takes data from other worksheets using the formula below....

=SUM(arraydata(INDEX(C$41:EZ$41,ROW()-ROW(C$3)+1)))

The formula only update when I click on it and press enter, which makes all the cells with this formula in update to. Is there a macro I can create to do this on the click of a button?

You can press F9 to force calculation or if you really want a button then create a Forms button and map a macro to it.

Sub Macro1()
'
    Calculate

 End Sub

Or if you can force the auto calc by changing anything on the sheet or, a specific column(s) or a select cell(s) with a macro similar to this which calls for a calculate when Cell C7 is changed. This macro belongs in the Sheet code not the Module

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

   On Error Resume Next
   Set rng = Application.Intersect(Target, Range("C7"))
   If Err Or rng Is Nothing Then
        Err.Clear
        Exit Sub
   End If
  
Calculate

ErrHandler:
Application.EnableEvents = True
End Sub

Excellent thanks for your help.