Forum Discussion
2 Way Cells
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
I wasn't as clear as I could have been. The numbers I put in the original description are just arbitrary. What I am trying to do is make some sort of equation so that I can manipulate, to make it simple for example, the percentage of down payment on a property vs. that amount which you lent. Say the total price of the property is $100,000 you start with cell A1=20% for down payment percentage, A2=80% for loan percentage, B1=$20,000 for money down and B2=$80,000 for loan amount. I want to be able to manipulate the numbers so that I can change it to 21% and the other 3 cells changed accordingly A2=79%, B1=21,000, and B2=79,000. Or change it to B2=$43,000 and the other cells change accordingly A1=57%, B2=43%, and B1=$57,000. Or change the price of the property and the 4 other cells change accordingly. This is just a basic example of what I want to use it for but there could be more cells such A1-A12 and B1-B12. I hope this clarifies.
maybe the attached sample can help..
the VBA is rather crude - but I think it works..