Let's say I have 3 columns each containing the same price, but in a different currency. I need to update the other two depending on which column has been updated. I presume I would need a base value in one of these currencies and somehow update this value when editing one of the cells in each column, then update the other columns by calculating the values using that base value. Is this possible to do in google spreadsheets? As far as I know each cell may contain only a single formula.
0
votes
2 Answers
1
votes
As far as I know each cell may contain only a single formula.
True. Furthermore, your cells can't contain formulas at all, given that you're going to edit the values manually.
Let me suggest you another approach to your situation:
[A] [B] [C] [D] [E]
VALUE CURRENCY VALUE IN EUROS VALUE IN DOLLARS VALUE IN POUNDS
1,00 Euros 1,00 1,29 0,79
10,00 Dollars 7,72 10,00 6,12
You'd have two editable columns (value and currency), and three automatically calculated columns. The currency column could even have a dropdown combo to select each value instead of having to type it (to do so, just define a validation rule for the column).
The formulas for these calculated columns would be quite easy. For example:
[C2]: IF(OR(A2="";B2="");"";A2*IF(B2="Euros";1;IF(B2="Dollars";0,772;1,26)))
Something like that. Of course, the conversion values should be moved to configuration cells, so they can be easily changed without editing the formula.
If you want/need to keep your sheet with only the 3 columns you mentioned, then what you need can't be done with simple formulas. You'll need to write a script which detects the edit event on a cell and updates the other two. I'm sure that it can be done, I'm just not so familiar with the Spreadsheets API to tell you how, but it must not be quite difficult.
