0

Next week I am going to travel to Europe and I wanted to have in a spreadsheet all my spendings, but as I will need to use both Euros and British Pounds, I need to have a way to set the currency used that time. I thought of using a dropdown list to select the currency, but I can't find a way to change the currency formatting of the amount cell based on the data entered in the list.

Can someone please help me make a script that can do this for me?

1

As requested this is one way to do it in google app scripts:

function onEdit(e){
  var sheetName = "Currency"; //name of sheet we want to adjust formatting
  var currencyCol = 1; //column A
  var amountCol = 2; //column B
  var defaultFormat = "[$$]#,##0.00";
  var currencyFormat = {"USD":"[$$]#,##0.00",
                        "POUND":"[$£]#,##0.00",
                        "EURO":"[$€]#,##0.00"};
  var r = e.range;
  if(e.source.getSheetName()==sheetName && r.getColumn() == currencyCol){ //we are assuming you will edit one cell at a time to simplify this
    var uf = currencyFormat[r.getValue()];
    uf = uf?uf:defaultFormat;
    r.offset(0,amountCol-currencyCol).setNumberFormat(uf);
  }
}

Personally I would rather just add a third helper column where I would put =if(A3="EURO",text(B3,"[$£]#,##0.00")) etc and have the column I type things in just be formatted as a regular number. Either way you are going to have to type or select the currency type, although if you had the keyboard to do it you could even just type in the currency symbol.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.