A = B+C; if I want to get B depending on A? 2

[davidrdguez]

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,

 

[pmover]

examine or try using the "Goal Seek" feature w/in XL or in other cells, enter the formula B = A - C with variables A & C.

it can also be done using nested if statements with one formula in the cell.

hope this helps and good luck!
-pmover

 

[davidrdguez]

Thank you for the fast answer.
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,

 

[IRstuff]

There is no non-destructive way of doing that directly on a sheet in Excel. It's something that TK Solver and Mathcad can do relatively easily, since that was a fundamental design requirement.



TTFN
faq731-376

 

[BigInch]

"I want to introduce 2 values (in any of the 3 cells) and get the third one"

Is this what you want?


If it ain't broke, don't fix it. If it's not safe ... make it that way.

 

[LiteYear]

Just musing:

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?

 

[IDS]

I actually like Biginches solution, but if you want to have the results in the same three cells as the input a macro is the only way (since you can't have a cell containing a formula and allowing data input without writing over the formula).

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/

 

[IDS]

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.

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/

 

[Hoaokapohaku]

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

 

[Hoaokapohaku]

I just made my post, and then see that IDS posted his while mine was in progress. Our approaches are similar. I used the Application.EnableEvents = False statement to avoid the potential looping problem that IDS mentioned. I used the user form to having to avoid taking additional steps on the worksheet. To deal with the same problem, IDS edits a second cell value on the worksheet. IDS used name ranges, I should have, but didn't. It would be easier to move from machine to machine if I had used named ranges.

 

[BigInch]

If allowed to use VBA, I think a UDF might go down easier than a macro.

If it ain't broke, don't fix it. If it's not safe ... make it that way.

 

[davidrdguez]

Nice answers!

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,

 

[rb1957]

one simple way would be to a worksheet with nine columns ...
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.