A cell can contain either a value or a formula, but not both.
This could only be done with a macro. Would you be interested in that?
Maybe you could use this event code...Yes please
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address(0, 0) = "B6" Then
Range("B7").Value = 600 - Range("B6").Value
ElseIf Target.Address(0, 0) = "B7" Then
Range("B6").Value = 600 - Range("B7").Value
End If
Application.EnableEvents = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Intersect(Target, Range("B6:B7")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target.Address = "$B$6" Then
Range("B7").Value = 600 - Target.Value
Else
Range("B6").Value = 600 - Target.Value
End If
Application.EnableEvents = True
End Sub
Under what circumstance?