Conditional Format Currency

[guyKn]

I have serveral price lists in £,$ and Euro's in excel.

I want to be able to automaticly change the format of the price lists to show £xxx $xxx etc dependeing on a value in a single field.

Any ideas?

Guy

 

[xlbo]

Don't think you can do this without code - if yuo are interested in coding this, it should be relatively simple

Pseudocode:

Worksheet_Change()
If target.address <> "Lookup Cell" then exit sub
Select Case Range("Lookup Cell")
case 1
'change format to £
case 2
'change format to $
case 3
'change format to Euros
case else
msgbox "Not a valid entry"
end select
end sub

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[TonyJollans]

Hi guyKn,

What exactly wants to change - I presume it's both the amount (based on an exchange rate) and the currency symbol? You might consider this approach ..

Somewhere out of the way, let's say in columns AA,AB,AC for now, have a table of exchange rates and currency symbols ..

Cell AA1: "Dollars"
Cell AB1: Exchange rate multiplier (base units to dollars)
Cell AC1: The dollar currency symbol

Cell AA2: "Pounds"
Cell AB2: Exchange rate multiplier (base units to pounds)
Cell AC2: The pound currency symbol

Cell AA3: "Euros"
Cell AB3: Exchange rate multiplier (base units to euros)
Cell AC3: The euro currency symbol

Somewhere else, for now let's say Z1, have your single value that you want the formatting to be dependent upon.

Now, with a base values in column A starting in A1, put the following in cell B1 ..

[blue][tt]=TEXT(A1*VLOOKUP(Z1,AA1:AC3,2,0),"[$"&VLOOKUP(Z1,AA1:AC3,3,0)&"0.00")[/tt][/blue]

.. and copy B1 down as far as your values in column A go.

Now, if you put "Dollars" in cell Z1, values in column B will be formatted in dollars; if you put "Euros" in Z1, values in column B will be formatted in euros, etc.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.