On Mon, 5 Sep 2005 08:00:19 -0500, Jan Jansens
wrote:
Hi, i've got a spreadsheet with a function i would like to duplicate:
There are 2 cells, one with the Yield of a security (in this case a
bond) and another cell with the price of the security (the same bond).
Lets say A1 holds the price and cell A2 holds the yield. Now Excel
functions Price() and Yield() use more or less the same arguments and
let you calculate the Price of a bond, based on a certain yield OR let
you calculate the yield of a bond, based on a certain price. So it's a
bit like the chicken and the egg...you need a price to calculate yield
or you need a yield to calculate a price.
In this spreadsheet i'm having they allow you to type in the Price of a
security in cell A1, which will give you a yield in cell a2. The nice
thing however is the following: if you type a yield in cell a2 it will
give you the price in cell a1. So based upon your input Excel looks at
the cell where you have input the value and puts a function in the other
cell....it calculates the result and puts the original function back
into the cell where you have just typed a value, allowing you to do
further calculations with that function....So even when you type a
value in a cell, somehow excel still knows what functions should behind
it once it has done it's calculations......Maybe i'm not explaining this
very well but if someone has any idea how you can do this i would love
to hear from you ! Thanks in advance!!!!
Without seeing the s/s I can't be certain, but it sounds like there's
probably some VBA code in operation here which is being driven by a
Range Selection Change Event.
i.e. when you enter a value in one cell, the Change event of that cell
is fired and puts a formula in the other cell, and vice versa.
You could check by looking in the VBA window ALT-F11, double clicking
on the sheet name in the VBA Project Explorer pane, and then seeing
what is in the Worksheet Selection Change pane over on the right.
Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
|