I'm making a body measurements spreadsheet that has to be useful internationally. I have a dropdown menu in a cell that allows the user to select their preferred 'height units' ("m" or "in"). Later, they are asked for their height as a numerical value. I want to add the unit text to the end of the number they enter in the height cell. This can be added after they enter the number, or appear fixed to the right-hand-side of the height cell and therefore appear after the numerical value automatically. Either is just fine. For example, if they select "in" from the dropdown, and then enter 65 in the height cell, it will automatically appear as "65 in" in the height cell. But, after 20h of trying everything, I'm no further ahead. Any ideas will be warmly welcomed! (PS: I know I can do this with two cells, but the layout will be affected adversely.)
-
1Welcome to Web Applications Stack Exchange. Please edit the question and clarify whether you want values to change in place. Add a table of sample data together with another table that shows your manually entered desired results.– doubleunaryCommented Nov 29 at 7:27
3 Answers
Just to clarify, you want the input cell for the height to automatically convert the input value to output the measurement value and the preferred unit, eg. user inputs 65 (for 65in) and as soon as the value 65 is entered, the spreadsheet will output "65in" in the same cell? To keep it simple, i'd make the "in" or "m" appear in the cell adjacent to the numerical value on the right, as soon as the option is selected by the user from the drop-down menu at the start, using a simple equal formula. say dropdown is in cell A1 and cell A5 is the input cell for the height, cell B5=A1. However when you later operate on the numerical input values, you can convert them (if need be) to a metric one and convert back the results to appear to the user as an imperial one.
-
Thank you so much for taking the time to answer. I initially did what you said using the cell on the right, but, it was the only one in a long vertical column that needed a unit, so it really screwed up the layout of the whole sheet. I was hoping it would do what you described: person puts in 65 and the cell automatically turns it into "65 in" or "64 m" (with the space) depending on the value of the previous dropdown. I'm sure this is an easy solve with AppScript, but I'm a complete novice at that so it's defeated me. :o(– AndrewCommented Nov 28 at 9:55
-
@Andrew ensure your question is clear so members don't invest time providing solutions that don't meet your needs. If you want an Apps Script solution, show what you've tried and what didn't work.– Blindspots ♦Commented Nov 29 at 13:38
Assuming that the number is in cell A2 and the unit is in cell B2, put this formula in cell C2:
=join(" "; A2; B2)
See join().
-
Thank you for that answer. The problem I have is that the formula is overwritten when someone enters the height data. So, say, they choose unit in A1, and enter height (as a number in A2), I want A2 to automatically read "height_value <space> unit". E.g. A1 = m, and the users enter 1.65 into A2. When they click return, or tab or move out of the cell, it converts itself to "1.65 m". Not sure why I'm so hung up on this. I think the fact that it almost certainly has an easy solution but I just can't figure it out!– AndrewCommented Nov 28 at 23:42
-
The result won't be overwritten if data entry is limited to columns
A2:B, because the results are in columnC2:C. To change or format the value in place, rather than in putting the result in a different cell, you need a script. Commented Nov 29 at 7:25 -
@Andrew please add the requirements from your comment as an edit to the question so it is better understood.– Blindspots ♦Commented Nov 29 at 13:27
-
Thank you. I'm trying to have the output (number + unit) appear in the same cell as the number is entered into.– AndrewCommented Nov 29 at 19:20
The following script will append the value in cell C4 to cell F5 when a number is entered in cell F5:
function onEdit(e) {
if (!e) throw new Error('Please do not run the onEdit(e) function in the script editor window. It runs automatically when you hand edit the spreadsheet. See https://stackoverflow.com/a/63851123/13045193.');
if (typeof value !== 'number' || e.range.rowStart !== 5 || e.range.columnStart !== 6) return; // cell F5
const unit = e.range.offset(-1, -2).getDisplayValue(); // cell C4
if (!unit) return;
e.range.setValue(value + ' ' + unit);
}
The script observes these onEdit(e) best practices.
-
Would be worth noting in the answer that this will not be a custom number format but convert the numbers to text strings. If the numbers will be reused this may be an issue and will need to be addressed in formulas.– Blindspots ♦Commented Nov 29 at 13:30