2 cells always sum to same value

[jkicker]

Where would i put this information so that i can change b6 OR b7 and the other would change itself:
b6 = 600-b7
b7 = 600-b6

I want the cells to always sum to 600 but be able to change their values individually.

 

[Fluff]

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?

 

[jkicker]

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?

Yes please

 

[Rick Rothstein]

Yes please

Maybe you could use this event code...

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

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Fluff]

Ok try

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

This needs to go in the relevant sheet module

 

[Fluff]

@Rick Rothstein
I think your code might take a while to run

 

[Rick Rothstein]

@Rick Rothstein
I think your code might take a while to run

Under what circumstance?

 

[jkicker]

speaking of running, normally with macros you need to run them manually, unlike formulas. this one runs because it was triggered by an event? or is there something in there that makes it always run?

 

[Fluff]

Under what circumstance?

You've added the EnableEvents False & True lines, so all should be ok now.

 

[Fluff]

Both codes will run automatically when you change either B6 or B7