A = B+C; if I want to get B depending on A?
A = B+C; if I want to get B depending on A?
(OP)
Hi there,
I tried to be specific with the subject but I don't know if I succeeded.
In excel, I have a formula (output, A) in one cell which is the addiction of other two (inputs B and C)
But, If I want to introduce a value in the formula (A) and I want to get the value of B with the same relation (B = A - C)??
It's like I have this relation: A = B + C
I want to introduce 2 values (in any of the 3 cells) and get the third one
Is this possible in Excel without a macro?
If not... is this possible with a macro?
Cheers,
I tried to be specific with the subject but I don't know if I succeeded.
In excel, I have a formula (output, A) in one cell which is the addiction of other two (inputs B and C)
But, If I want to introduce a value in the formula (A) and I want to get the value of B with the same relation (B = A - C)??
It's like I have this relation: A = B + C
I want to introduce 2 values (in any of the 3 cells) and get the third one
Is this possible in Excel without a macro?
If not... is this possible with a macro?
Cheers,





RE: A = B+C; if I want to get B depending on A?
it can also be done using nested if statements with one formula in the cell.
hope this helps and good luck!
-pmover
RE: A = B+C; if I want to get B depending on A?
The "goal seek" is nice; I didn't know it; but it is not what I was looking for; it takes some time
The nested IF idea could work, I have to check.
Also making different scenarios with pulldown menus to choose in each case what we want to introduce....
The best solution would be having the 3 cells, and just adding 2 inputs getting the 3rd one
Cheers,
RE: A = B+C; if I want to get B depending on A?
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: A = B+C; if I want to get B depending on A?
Is this what you want?
If it ain't broke, don't fix it. If it's not safe ... make it that way.
RE: A = B+C; if I want to get B depending on A?
You could have the values in one row: A, B, C
And the formulas in the next row: =B+C, =A+C, =A+B
And a button labelled "Update" tied to a macro that simply copies the values in the second row into the first row.
Then, for everytime you want to run your calculation: hit Update, then change one or 2 values in the first row, and observe the effect in the second row.
Would that work?
RE: A = B+C; if I want to get B depending on A?
The attached spreadsheet has a macro that runs whenever anything is changed on Sheet 1. If you delete the contents of A, B, or C it will update the value based on the other two cells. If you enter a value in any of the three cells you get a message to delete one of the cell values to get it to update.
If you are playing with Worksheet_Change macros note that it is pretty easy to set up an infinite loop that will keep going until you shut the application down.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: A = B+C; if I want to get B depending on A?
Further to that comment, Chip Pearson has a good detailed article about the Worksheet_change event, and how to control it, at:
http://www.cpearson.com/excel/Events.aspx
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: A = B+C; if I want to get B depending on A?
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
RE: A = B+C; if I want to get B depending on A?
RE: A = B+C; if I want to get B depending on A?
If it ain't broke, don't fix it. If it's not safe ... make it that way.
RE: A = B+C; if I want to get B depending on A?
The idea is having only 3 cells
I see one needs some programming to do so; I don't know about it my colleagues do.
Thank you all
regards,
RE: A = B+C; if I want to get B depending on A?
A (=B+C), B, C, A, B(=A-C), C, A, B, C(=A-B)
the idea being to have a simple sete of triples so you can readily see what dat was provided and which was solved.
use one row for data entry, entering either B & C, or A & C, or A & B; so that each row has only three cells filled in.
then on the sheet where you want only three cells you could either sum the three "A" cells, or max the three "A" cells, and similarly for B and C.