Calc: How to have two input cells convert measurment units between each other?

[Zoidman]

Version: 4.2.6.2
Build ID: 185f2ce4dcc34af9bd97dec29e6d42c39557298f

I am making a spread sheet, and want to give the user the option to input data in inches, millimeters or both (mixed). I have an inch and a mm column and want both filled out when data is entered in either one. So if I put an inch value in an inch cell (A1), I want the adjacent mm cell (A2) to convert to mm which is easy (=A1*25.4), but I tried to put =A2/25.4 in A1 and got an error message. How do I make it work in both directions?

The whole idea is to let a machinists enter measurements from parts plans that have different units of measure without having to convert them first, and giving them the option to use whichever their equipment allows.

[Rugslug]

I had once done something similar in excel with a macro that used “OnChange” to do exactly what you want. Somebody with Macro experience could come up with a solution. When you type in a value in A1, A2 would update automatically and vice versa. I’m not sure how adaptable this would be for multiple rows.

Another solution to try involves 4 columns. Col A is your input units, B is a drop down list (Data Validity) of your unit choices (mm or inch), C is an IF statement to use appropriate choice of Col B and conversion factor, Col D is your Results Unit using an IF statement to be the opposite of Col B units. This could be easy to use for multiple rows.

Test File: Conversions.ods

[Lupp]

I simply show as I would do it: Offer two cells for entry per item, the first one if mm is to use, the second one in case of inches. The respective other one is kept empty. In two adjacent cells there will be calculated and displayed both versions on the base of formulae.
See: ask43863UnitsAlternative001.ods

But Rugslug’s suggestion may be preferable.