Enter value, convert, read result from same cell
 

Hi,

I am attempting to build a travelling esxpenses calculator capable of
handling several variables; distance travelled, fuel economy (mpg), price per
litre of fuel.

I would like the option of being able to enter a value eg 120.3 (km) and
then convert this figure to miles for the rest of the formula. so I would
like a button that says "convert to miles" or similar that when activated
returns the contents of the cell as a milage (in this instance approx 75.2).

I would also (of course) like the option of entering miles directly in the
cell and for the rest of the formula to pick it up.

I have worked out that I can achieve this using two cells and a circular
function with a single iteration (entering miles gives km and vice versa) but
as soon as a result is displayed it overwrites the formula for that cell and
can't be undone so any mistakes (for instance entering mileage values in the
km cell) mean a complete fresh start.

I understand it is possible to write a macro that can deliver results in the
same same (input) cell but all my experiments have failed. Can anyone
explain to me what I need to do, or what other options there are? Thanks,

Stephen

Enter value, convert, read result from same cell
 

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"sjbegley" wrote in message
...
Hi,

I am attempting to build a travelling esxpenses calculator capable of
handling several variables; distance travelled, fuel economy (mpg), price
per
litre of fuel.

I would like the option of being able to enter a value eg 120.3 (km) and
then convert this figure to miles for the rest of the formula. so I
would
like a button that says "convert to miles" or similar that when activated
returns the contents of the cell as a milage (in this instance approx
75.2).

I would also (of course) like the option of entering miles directly in the
cell and for the rest of the formula to pick it up.

I have worked out that I can achieve this using two cells and a circular
function with a single iteration (entering miles gives km and vice versa)
but
as soon as a result is displayed it overwrites the formula for that cell
and
can't be undone so any mistakes (for instance entering mileage values in
the
km cell) mean a complete fresh start.

I understand it is possible to write a macro that can deliver results in
the
same same (input) cell but all my experiments have failed. Can anyone
explain to me what I need to do, or what other options there are?
Thanks,

Stephen