1. Select the developer tab.

Start your Macro Recorder

Select your cell with the formula

Click into the formula bar at the top of excel and then press enter.

Stop the Macro Recorder.

2. Select the developer tab
Select run Maco
Select Macro1 and the EDIT.

You should see something like:


Sub Macro1()
'
' Macro1 Macro
'

'
    Range("E9").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-8]C:R[-1]C)"
End Sub

Edit that code to something like:

    Range("E9").FormulaR1C1 = "=SUM(R[-8]C:R[-1]C)"
Copy that code and close the Macro Module.

Right Click on the Sheet Name at the bottom of excel and select view code

A module will open.

Select the Listbox at the top left that Says General. Make that read Worksheet.

Select the Listbox at the top Right that Says SelectionChange. Make that read Change.

You will see this code:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Paste your Macro in the middle of that code and then close the Module:


Private Sub Worksheet_Change(ByVal Target As Range)
    Range("E9").FormulaR1C1 = "=SUM(R[-8]C:R[-1]C)"
End Sub

Ok you are done. From Now on it is impossible to change the formula in your cell.