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?

2 Answers 2

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.

-1

Not quite sure how this script should work because it comes up with error. Do you have some ideas how to make it run, im looking for something to change currency type from dropdown list.

1
  • Sorry, but never ended up using this script and it was quite a while ago that I asked this question :(
    – Spiwocoal
    Aug 13, 2021 at 2:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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