olznew.blogg.se

NOTE: Although you can open the file in some earlier versions of Excel, the conditional formatting will only work correctly in Excel 2010. In the Conditional Formatting section, look for: CF0003 – Conditional Formatting for Currency Symbol To see the formulas and conditional formatting, you can download the sample file from the Contextures website. The pricing and currency symbol for the selected country will be displayed. To test the conditional formatting, select a different country from the data validation drop down list. Click Format, and in the Format Cells dialog box, select the Currency format, and the applicable symbol.In the ‘Format values where this formula is true’ box, type a formula that checks the selected currency (CurrSel).In the New Formatting Rule dialog box, select ‘Use a formula to determine which cells to format’.On the Ribbon’s Home tab, click Conditional Formatting, then click New Rule.On the Order sheet, select the currency cells - D4:E10 in this example.Next, you’ll format the cells on the order form, so the currency symbol changes, depending on the country selected. The adjusted price will show up in the order sheet. In the price list, multiply the base price by the selected country’s exchange rate, to get the adjusted price for each product. Use a VLOOKUP formula to return the exchange rate and currency for the selected country.Ĭell G1 is named ExchRate and G2 is named CurrSel. On a separate worksheet, create a list of countries, with each country’s exchange rate and currency. Conditional Formatting for numbers was available in Excel 2007, but didn’t work correctly.

The technique show here will only work in Excel 2010 and later versions. See how to use those settings, you can change the number format based on a cell’s value, to show a specific currency for the country that’s selected. In Excel 2010 and later, you can use conditional formatting for currency symbol changes.

If you sell products in several countries, you might want to show the prices in different currencies.