Ask Your Question

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

asked 2014-12-19 21:00:18 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Version: 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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-07 02:52:11.790327

2 Answers

Sort by » oldest newest most voted

answered 2014-12-20 16:49:02 +0200

Rugslug gravatar image

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

edit flag offensive delete link more

answered 2014-12-21 00:11:03 +0200

Lupp gravatar image

updated 2014-12-21 00:12:52 +0200

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.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2014-12-19 21:00:18 +0200

Seen: 873 times

Last updated: Dec 21 '14