Forum Discussion
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 cell it can change all the other cells. For example, if I have Column A with different percentages and Column B with different numbers, such as A1=30% B1=15 A2=70% B2=35. How do I input an equation so that you can change any of those 4 cells and the numbers in the other 3 changes accordingly.
The closest answer I came to was creating a dynamic link between cells with VBA but that didn't work; Maybe I didn't do it correctly. Thanks for any help!
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
14 Replies
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..
Mr. Dolinar
pls find as attached a sample of what I perceived to be your point.
in all those colored cells - you input any value and the other 3 cells changes correspondingly.
it may look crude - but I think it suits your need.
I intentionally locked all other cells - there is no password for the protection.
HTH
- latest attachment...
You won't be able to use any sort of formula or equation in the cells. When you change a value in a cell, it would overwrite the formula. The code that I provided above will only work with 2 rows, and as stated earlier, if you want to split it more ways than that, the code would get extremely complicated (beyond my level of comprehension). If you format cells A1, and A2 as Percentage, and B1, B2, and E1 as Currency, it should work for you. Here is my version. you can change E2 to the total amount, and then edit any of the other 4 cells, and the remaining cells will update to the total.
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 SubBob,
This response seems to be working fine in my spreadsheet. Thank you for the help!
Ryan
You're welcome. I'm glad it's working for you.
I have an idea.
Using VBA to write macro while changing A1 to B2 (either cells). You may refer to the attached file. The main code is stored in <workbook>. However, if you are considering more than 4 cells, the coding will be very complicated.
Also, I am unable to clear the cells. That's why I included another module to clear A1 to B2.
- how about the values that Mr. Dolinar stated?
maybe He should elaborate further to make it clear..
meaning - what is the relationship of each cell to each other.I do not know the relationship between the cells. I think he can modify the code by changing, for example, Range.("A1") to Range("A1").formula blah blah blah.
- change any of those 4 cells and the numbers in the other 3 changes accordingly.
There might be a "circular reference" problem here...
