Converting Units Both Ways

[cgow]

I am interested in having a program that claculates results using metric values but the company I work for uses both metric and English units.
The problem I am having is that sometimes the metric is known and sometimes the English is known.

Here is an example of what I have so far.

A B C D
1 __ in __ mm
2 __ in __ mm

So if I have the conversion from in. to mm set up in column C eveything works fine if I input the values in A. But I would also like to be able to input the equivalent values in column C and convert back to in. if needed.
I tried to put the appropriate conversions in each cell, both columns A and C(which creates a circular reference), but if at first I input the english value in colum A and then during the next example I know the metric, the 'back conversion' is erased from the cell and therefore doesn't accomplish what I wanted. Is there any way to have formulas working in the background so to speak so that it will convert in either direction without erasing the formula?

Thanks for your help, I hope my problem is clear enough for some help.

 

[Brian from Maui]

I am interested in having a program that claculates results using metric values but the company I work for uses both metric and English units.
The problem I am having is that sometimes the metric is known and sometimes the English is known.

Here is an example of what I have so far.

A B C D
1 __ in __ mm
2 __ in __ mm

So if I have the conversion from in. to mm set up in column C eveything works fine if I input the values in A. But I would also like to be able to input the equivalent values in column C and convert back to in. if needed.
I tried to put the appropriate conversions in each cell, both columns A and C(which creates a circular reference), but if at first I input the english value in colum A and then during the next example I know the metric, the 'back conversion' is erased from the cell and therefore doesn't accomplish what I wanted. Is there any way to have formulas working in the background so to speak so that it will convert in either direction without erasing the formula?

Thanks for your help, I hope my problem is clear enough for some help.

There probably is a better way, but in column E have a drop down or an equivalent and filter the conversion with an IF function.

=IF(E1="IN",YOUR FORMULA TO CONVERT TO IN,YOUR FORMULA TO CONVERT TO MM)

 

[Greg Truby]

You could just drop this into the WS's code module and be done with it. (See below if you don't know how to do that.)

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Column = 1 <SPAN style="color:#00007F">Then</SPAN>
        Target.Offset(, 2) = Target * 25.4
    <SPAN style="color:#00007F">ElseIf</SPAN> Target.Column = 3 <SPAN style="color:#00007F">Then</SPAN>
        Target.Offset(, -2) = Target / 25.4
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>

To add code to a worksheet:

1. Right-click the tab for the sheet to which you want to add the code.
2. Click on View Code... from the popup menu.
3. Copy and Paste code or
   <ul type=disc>
4. Pick Worksheet from the left combobox at the top of the code pane
5. If SelectionChange (the default) is not the correct event, then click the appropriate event from the combobox at the right

[/list]

{Edit}Forgot my manners - Welcome to the board! And "hi" to you, Brian, ain't posted with you for a while.{EndEdit}
HTH