Automatic formatting for currencies

Question

Automatic formatting for currencies

When a cell is left on automatic formatting, the formatting will adapt to its content, and so will automatically change the currency of the cell to reflect the content. For example, if the cell contains a stock price it will change from $ to CA$ if the stock is listed on the Toronto Stock Exchange. However, any change to the formatting will nullify these automatic formatting advantages.

For example:

say cell A1 has a picker that allows me to choose from different currencies. B1 just says "$1 =", and C1 (set to automatic) will give me the exchange rate, correctly formatted with the currency selected in A1 so that when I change my selection the formatting of C1 changes to reflect the new currency. So far so good. But say I wanted to enable "Thousand Separators" or maybe add some decimals, then the formatting immediately switches to hardcode the current currency selection as the format of the cell, and changing A1 will no longer automatically switch the formatting of C1 to the correct currency.

Is anyone aware of a way to adjust the formatting of currencies while retaining the automatic changing to the correct currency?

iMac 27″, macOS 13.6

Posted on Apr 24, 2024 9:55 AM

Reply

Answer 1

Badunit

Level 7

26,000 points

Apr 24, 2024 8:12 PM in response to thegoddelusion

Like you've seen, if you mess with the settings it locks in the currency symbol too. Bu this might work:

CURRENCYCONVERT(STOCK(ACME.CN)) will return the price in your local currency. It appears that if you set the thousands separator and other things on that cell, you can do a secondary conversion in another cell and it will have the same settings.

A1 has a valid currency code

C1 =CURRENCYCONVERT(STOCK(ACME.CN)) (which converts the price to USD for me).

Format C1 with the thousands separator and decimals.

D1 = CURRENCYCONVERT(C1,A1) will convert the currency from USD to whatever is in A1 and keep the settings of C1 (or so it seems).

I think this works but I only did a limited test. Or maybe you'll find a different way to do it.

Reply