Change cells based on value of other cells

[foxtrot1972]

Probably a really simple formula for this. I have two cells, both of which I want to be able to change. When I change them, the changes affect other cells in the file which is fine. These two specific cells must always add up to 100 though, and so when I change one and set it at say, 70 - I want the other one to automatically update to 30 so that I don't continually have to keep changing both cells. I'm sure it's possible ... what's the solution?

[Kaper]

You shall use macros for that. In particular, you shall write event handler for worksheet change event. There are several examples on this forum and on the net how to do it

[Kaper]

If you are not sure how to implement this advice, see attached file (remember to allow macro execution)
Focus on Sheet1 (Arkusz1) code - right click on Arkusz1 tab and select display code to see it. Change A1 and B1 to your "connected cells" addresses

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Union(Range("A1"), Range("B1"))) Is Nothing Then
  Application.EnableEvents = False
  If Not Intersect(Target, Range("A1")) Is Nothing Then
    Range("B1").Value = 100 - Range("A1").Value
  Else
    Range("A1").Value = 100 - Range("B1").Value
  End If
  Application.EnableEvents = True
End If
End Sub