how prevent formula in cell from deleting when deleting value????

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

example: =sum(a1-b1-c1) answer and formula in cell d1
if i delete the answer to this formula in cell d1 it also deletes
the formula. i want the formula to stay in the cell d1
how can formula be locked or protected in cell d1 and still make
and delete value entries in cell d1 ?????
 
Your message isn't very clear. If you want the formula to remain in D1,
then it will continue to calculate.

If you mean that you want to override the formula, but have it return if
you delete the value used to override it, that will require an event
macro. One way:

Put this in your worksheet code module (right-click the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If .Address(False, False) = "D1" Then
If IsEmpty(.Value) Then
Application.EnableEvents = False
.Formula = "=A1-B1-C1"
Application.EnableEvents = True
End If
End If
End With
End Sub

(Note that the SUM() in your formula is superfluous).
 
Back
Top