BigInch's solution is good, but you must use an extra cell and you must edit more than one cell for a result. The VBA option below also has both advantages and disadvantages when compared to Big's approach.
The advantage is that you need no more than your three cells (for A,B, and C) values, and a change to any one of them will change the values of all three. As the code is written, it enters the correct value in each cell in a manner so that A+B=C always (or C-B=A, or whatever), but it doesn't enter a formula in any of the cells. The formula isn't needed because the determination of what calculations are needed and the calculations themselves are all done within the VBA procedure. If you want your worksheet to show a formula for the missing input variable, the code can easily be edited to put a formula rather than a value but it is not necessary.
The disadvantages are you will have to copy and paste the code into the correct place in your workbook using the VB editor and you must use the editor to copy "UserForm2" to your workbook. You must also edit the cell references in the code so that they refer to the cells containing the A,B, and C values in your worksheet. Users of the workbook other than yourself may not understand how or why some values are changing without formulas or making manual edits.
If you choose to use this solution, these are the steps:
1) To avoid confusion with too many workbooks open, close all workbooks except the workbook where you intend to use this macro, which should be opened.
2) Open the attached workbook, ABC.xlsm. If it gives you any security warnings, that's because it contains a macro. Open it as macro enabled.
3) Activate your workbook, and on the sheet where the cells for your ABC values are located, right click on the worksheet tab and select "View Code". The VBA editor will start.
4) On the left side of your screen, you should see the Project Explorer window, and in that window you should see the two files you have opened. Under "ABC.xlsm" you will see a "Forms" folder, and in that folder, you will see "UserForm2." Click and drag Userform2 to your workbook. The forms folder should appear in your workbook, with UserForm2 inside the folder.
5) In the Project Explorer window, under ABC.xlsm, double click on Sheet1. You will see the code appear on the right side of your screen.
6) Select the code from ABC.xlsm, sheet1, and copy to the clipboard.
7) In the Project Explorer window, double click on the sheet in your workbook where your ABC values are located. You will see it in the folder called "Microsoft Excel Objects."
8) Paste the code in the area on the right side of the screen in the VBA editor.
9) Edit the cell references so that they refer to the cells in your workbook for A, B, and C. Be sure to not delete the $ signs. Hint: Select a reference you must edit, select "Replace" from the edit menu, enter the correct info in the proper box (it's self explanatory), and click "Replace All."
9) Close the VBA editor.
10) Save your worksbook. If it is not already a macro-enabled workbook, use "Save As" and change the file type to macro enabled.
11) You are done! Try changing values for A, B, or C and make sure it works. If it doesn't, try closing the workbook after saving and re-opening, and make sure you open it as macro enabled.
That may sound complicated, but it's not. It should take a whole lot less time to do than it took to type.
For those that want to see the code without firing up the VBA editor:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ResetEvents
Application.EnableEvents = False
If Target.Address = "$A$2" Then
Range("$B$2").Value = Range("$b$2").Value
Range("$C$2").Value = Range("$A$2").Value + Range("$b$2").Value
ElseIf Target.Address = "$B$2" Then
Range("$A$2").Value = Range("$A$2").Value
Range("$C$2").Value = Range("$A$2").Value + Range("$b$2").Value
ElseIf Target.Address = "$C$2" Then
UserForm2.Show
End If
ResetEvents:
Application.EnableEvents = True
End Sub
Talk To Other Members
Read More Threads Like This One