You cannot do this without code. If you change A2 to a new sales price, your formula gets replaced - the same would be true for the profit margin.
The following code assumes that your cost is in cell C2.
Right click on the sheet your are using, and choose view code. Paste the following into the code box.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A2"), Target) Is Nothing Then
MsgBox "A2 changed"
Application.EnableEvents = False
Range("B2") = "=(A2-C2)/C2"
Application.EnableEvents = True
Else
If Not Intersect(Range("B2"), Target) Is Nothing Then
MsgBox "B2 changed"
Application.EnableEvents = False
Range("A2") = "=C2*(1+B2)"
Application.EnableEvents = True
Else
Exit Sub
End If
End If
End Sub
Bookmarks