I wish:
If user chooses to enter a volume in Liters in “A1” … then update “A2” with the equivalent of gallons.
If user enters Gallons into “A2” - then update A1 to reflect that …in liters.
Of course - this must not become a circular forever-loop, but only respond to manual entry or actual change.
Spreadsheets don’t work like this. Use 2 input cells for gallons and for liters and 2 formula cells for the converted liters and gallons.
…or one cell for a numeric value and the second with a drop-down list for selecting a unit of measure, and the formula will convert the entered value to the unit of measure in which you want to keep records. You don’t want to keep records in gallons and liters and tons, do you?
This isn’t just an “or”. The solution given by @Villeroy must anyway be completed by means to scotch the entering of incompatible values into dependent (“alternative”) cells.
See also attached commented example.
The best way? Abandon outdated units. Exclusively use the international system (SI).
(That’s not the final solution for everything, however.)
disask83589hardcodedThingNotRecommendable.ods (24.7 KB)
Nothing would please me more than to abandon the ridiculous US units.
The issue is: This is a “weight and balance” sheet for an aircraft.
If the tank volume of a given tank is read out - then it’s done in the stupid-age units of US.gallons.
If a crew fills an empty tank to the max, he enters -depending on the tank, for example, 200liters.
Workflow is normally: enter liters, or calculate gallons to liters, then enter liters.
I do understand perfectly that A1 and A2 in my request can’t hold formulas, or be circularly updating each other. Yet - for sanity - I would have A1 and A2 always display the same volume - in different units.
My real request is really for trigging a macro - only when the user edits a cell…
Macro “A_one” would respond to user-made change in A1 - and update A2.
Macro “A_two” would respond to user-made change in A2 - and update A1.
The main goal is then to run a macro only when a cell is edited by the user (and not the other macro)
Sigh. How many Mars Climate Orbiters still to lose?
But actually I doubt if a spreadsheet can at all be used reliably enough in your case.
If so the best idea should be to enter compounds consisting of a decimal number and the shorthand for the used unit, and to calculate the values using liters in one column and using lqGals in a second one.
The attached example demonstrates how REGEX() can be used to evaluate the compounds.
nextSuggestion.ods (13.4 KB)