Change currency symbol of multiple cells depending on input into another cell

**shaunnehughes** · 05-08-2010, 11:36 PM

Basically I want the currency symbol to change between $ and ¥ in cells U32:u40 and Q32:Q34 in Quotation! worksheet depending on whether the value of summary!d6 is Australian dollars or Japanese Yen.
Ideally the solution would also allow commas as "thousand seperators" ie. 1,000,000,000 and allow calculations to be made rather than Excel just thinking its text.

As you will see I found some code which helped another user on this site and I tried to tailor it to my worksheet without success.

Would really appreciate your help.

**DonkeyOte** · 05-09-2010, 02:59 AM

Looking at your code - the Case tests don't tie out to the currency your applying.

I'd also say that it's a good idea to force both criteria and test into a common case (lower/upper), eg:

Please Login or Register  to view this content.

If the formats themselves are the problem post back and let us know accordingly.

**shaunnehughes** · 05-10-2010, 12:50 AM

Thank you very much for your quick reply.
It worked beautifully so I am very grateful.

I just made some minor changes to suit the required style as you will see.

There is only one small issue.
If the value of the cell is zero the A$ or yen symbol still appears.
Is there any way of changing the code so that when the value is zero the cell is left blank?

I tried to do it myself as you will see from the attachment but without success.

**DonkeyOte** · 05-10-2010, 03:27 AM

I'm rushing a bit so I confess I've not looked at the file but in general terms custom formats can be seen to comprise four parts:

format for positive; format for negative; format for zero; format for text

so it follows that if you want a blank simply add two semi-colons to the end of each of your format strings
(technically you don't need both - one would suffice - but it's perhaps more transparent that way)

for more info. on formatting etc: http://www.ozgrid.com/Excel/CustomFormats.htm http://simoncpage.co.uk/blog/2008/09...er-formatting/