Smart questions
Smart people
 Find A ForumFind An Expert
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Remember Me

Are you an
Engineering professional?
Join Eng-Tips now!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Just copy and paste the

#### Feedback

"...this web site is a 'Godsend' for me. If I have a programming problem that I'm unable to solve, I'll get a sensible reply in no time. It's really great!..."

#### Geography

Where in the world do Eng-Tips members come from?

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

 Forum Search FAQs Links Jobs Whitepapers MVPs
 davidrdguez (Mechanical) 2 Aug 12 11:33
 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 (Mechanical) 2 Aug 12 11:47
 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 (Mechanical) 2 Aug 12 12:29
 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 (Aerospace) 2 Aug 12 12:50
 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.
 (2)  BigInch (Petroleum) 2 Aug 12 14:57
 "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. http://files.engineering.com/getfile.aspx?folder=74f1ffc6-3d7f-40bc-808c-72
 LiteYear (Computer) 2 Aug 12 20:47
 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 (Civil/Environmental) 2 Aug 12 21:31
 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/ http://files.engineering.com/getfile.aspx?folder=b65366c9-bbc9-4ba5-b6d5-3b
 IDS (Civil/Environmental) 2 Aug 12 22:19

#### Quote:

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 (Geotechnical) 2 Aug 12 22:20
 Hoaokapohaku (Geotechnical) 2 Aug 12 22:39
 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 (Petroleum) 3 Aug 12 0:22
 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 (Mechanical) 3 Aug 12 6:28
 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 (Aerospace) 13 Aug 12 13:55
 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.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!