Dependent formulae

[mrs_excel11]

Hi,
I have two cells each of which has a multivariable formula in it to calculate price and yield. Amongst these variables are the price and the yield themselves. i.e. the price depends on the yield and the yield depends on the price. I am looking to create a system of two cells so that if I enter the value of the price into the "price cell", the value of the yield is returned, and if I enter the value yield into the "yield cell", the value of the price is returned. How do I integrate this into my sheet ? Your help would be most appreciated. Many thanks.

 

[energman58]

So - you have two formulas one says something like

Price=F(Yield)

And the other is

Yield=F(Price)

Any you want to do something so that if you enter price in one cell the second cell is populated with the calculated value of the yield and vice-versa?

You cant do this with a formula (obvioulsly) as it would be circular. How I would do is with a change event procedure that kicked off a procedure to calulate the value for the other cell and populate it any time either of the two cells are changed.

If you look here

http://www.cpearson.com/excel/Events.aspx

there is a load of information to get you started

But beware - as you will be changing a cell that will kick off an event procedure you could trigger an infinite loop which will crash your procedure. You have to set Application.EnableEvents to FALSE in your procedure before you change the cell and then set it to true again once you have done it to avoid this problem.