Forum Discussion

Copper Contributor

Aug 21, 2018

Solved

2 Way Cells

Sorry if this is on the forum already, I don't exactly know what to call it and answers I've searched for haven't helped me yet. I want to make my spreadsheet so if you change a number is one ce...

excel

BobOrrell
Aug 22, 2018
Without any details, we are forced to make assumptions:
1. Column A should add up to 100%
2. Column B should add up to the original number
3. You will not always want it calculated from 50
4. Cells A1 and A2 are formatted as Percentage

This should work for you, and give you some flexibility. Sheet 1, Cell E1 should be the "total" (start with 50 to match your example in the original post). The following should be saved in the code for the worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False 'Stops listening for changes until the script is finished. Keeps from getting stuck in a loop

If Not Intersect(Target, Range("$A$1:$B$2,$E$1")) Is Nothing Then 'If the changed cell is in the target range, proceed
If Range("$E$1").Value = 0 Then 'If cell E1 is 0, set the other 4 cells to blank, and skip to the end of the script
For Each c In Range("A1:B2")
c.Value = ""
Next
GoTo 9999
End If
Select Case Target.Address
Case "$A$1"
Range("$B$1").Value = Range("$A$1").Value * Range("$E$1").Value
Range("$B$2").Value = Range("$E$1").Value - Range("$B$1").Value
Range("$A$2").Value = Range("$B$2").Value / Range("$E$1").Value
Case "$A$2"
Range("$B$2").Value = Range("$A$2").Value * Range("$E$1").Value
Range("$B$1").Value = Range("$E$1").Value - Range("$B$2").Value
Range("$A$1").Value = Range("$B$1").Value / Range("$E$1").Value
Case "$B$1"
Range("$A$1").Value = Range("$B$1").Value / Range("$E$1").Value
Range("$B$2").Value = Range("$E$1").Value - Range("$B$1").Value
Range("$A$2").Value = Range("$B$2").Value / Range("$E$1").Value
Case "$B$2"
Range("$A$2").Value = Range("$B$2").Value / Range("$E$1").Value
Range("$B$1").Value = Range("$E$1").Value - Range("$B$2").Value
Range("$A$1").Value = Range("$B$1").Value / Range("$E$1").Value
Case "$E$1"
Range("$B$1").Value = Range("$A$1").Value * Range("$E$1").Value
Range("$B$2").Value = Range("$A$2").Value * Range("$E$1").Value
Range("$A$1").Value = Range("$B$1").Value / Range("$E$1").Value
Range("$A$2").Value = Range("$B$2").Value / Range("$E$1").Value
End Select

End If

9999:
Application.EnableEvents = True 'Resumes listening for changes to the target cells

End Sub

BobOrrell

Iron Contributor

Aug 22, 2018

Without any details, we are forced to make assumptions:

1. Column A should add up to 100%

2. Column B should add up to the original number

3. You will not always want it calculated from 50

4. Cells A1 and A2 are formatted as Percentage

This should work for you, and give you some flexibility. Sheet 1, Cell E1 should be the "total" (start with 50 to match your example in the original post). The following should be saved in the code for the worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False 'Stops listening for changes until the script is finished. Keeps from getting stuck in a loop

If Not Intersect(Target, Range("$A$1:$B$2,$E$1")) Is Nothing Then 'If the changed cell is in the target range, proceed
If Range("$E$1").Value = 0 Then 'If cell E1 is 0, set the other 4 cells to blank, and skip to the end of the script
For Each c In Range("A1:B2")
c.Value = ""
Next
GoTo 9999
End If
Select Case Target.Address
Case "$A$1"
Range("$B$1").Value = Range("$A$1").Value * Range("$E$1").Value
Range("$B$2").Value = Range("$E$1").Value - Range("$B$1").Value
Range("$A$2").Value = Range("$B$2").Value / Range("$E$1").Value
Case "$A$2"
Range("$B$2").Value = Range("$A$2").Value * Range("$E$1").Value
Range("$B$1").Value = Range("$E$1").Value - Range("$B$2").Value
Range("$A$1").Value = Range("$B$1").Value / Range("$E$1").Value
Case "$B$1"
Range("$A$1").Value = Range("$B$1").Value / Range("$E$1").Value
Range("$B$2").Value = Range("$E$1").Value - Range("$B$1").Value
Range("$A$2").Value = Range("$B$2").Value / Range("$E$1").Value
Case "$B$2"
Range("$A$2").Value = Range("$B$2").Value / Range("$E$1").Value
Range("$B$1").Value = Range("$E$1").Value - Range("$B$2").Value
Range("$A$1").Value = Range("$B$1").Value / Range("$E$1").Value
Case "$E$1"
Range("$B$1").Value = Range("$A$1").Value * Range("$E$1").Value
Range("$B$2").Value = Range("$A$2").Value * Range("$E$1").Value
Range("$A$1").Value = Range("$B$1").Value / Range("$E$1").Value
Range("$A$2").Value = Range("$B$2").Value / Range("$E$1").Value
End Select

End If

9999:
Application.EnableEvents = True 'Resumes listening for changes to the target cells

End Sub

Ryan Dolinar

Copper Contributor

Aug 27, 2018

Bob,

This response seems to be working fine in my spreadsheet. Thank you for the help!

Ryan

BobOrrell
Iron Contributor
Aug 29, 2018
You're welcome. I'm glad it's working for you.
Lorenzo Kim
Bronze Contributor
Aug 27, 2018
welcome - glad to help..