SOLVED

Formatting a number by reference

%3CLINGO-SUB%20id%3D%22lingo-sub-1441872%22%20slang%3D%22en-US%22%3EFormatting%20a%20number%20by%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1441872%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20column%20consisting%20of%20all%20data%20validation%20drop%20downs.%26nbsp%3B%20Clicking%20on%20the%20arrow%20gives%20you%205%20choices%20and%20these%20are%20all%20currency%20format%20option%20-%20USA%2C%20CAN%2C%20HDK%20etc.%26nbsp%3B%20Next%20to%20that%20column%20are%20where%20the%20user%20inputs%20values.%26nbsp%3B%20This%20is%20a%20s%2Fsheet%20being%20used%20globally%20so%20that%20is%20why%20I%20have%20different%20currencies%20to%20deal%20with.%26nbsp%3B%20What%20I%20want%20is%20a%20conditional%20formatting%20process%20that%20will%20format%20the%20cell%20where%20the%20number%20is%20tped%20in%20based%20on%20the%20currency%20designation%20in%20the%20validated%20cells.%26nbsp%3B%20So%2C%20for%20example%2C%20cell%20A3%20has%20the%20drop%20down%20and%20the%20user%20picks%20%22CAN%22%20because%20they%20are%20going%20to%20enter%20in%20data%20as%20Canadian%20dollars.%26nbsp%3B%20In%20Cell%20B3%20they%20type%20in%20250%2C000.%26nbsp%3B%20I%20want%20the%20number%20formatted%20as%20Canadian%20dollars%20like%20this%20-%20CAN%20250%2C000.%26nbsp%3B%20If%20it%20was%20Brazil%20and%20A3%20was%20BRL%2C%20then%20I%20want%20to%20see%20BRL250%2C000%20in%20B3.%26nbsp%3B%20I%20don't%20see%20a%20way%20to%20do%20this%20in%20conditional%20formatting.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1441872%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1441937%22%20slang%3D%22en-US%22%3ERe%3A%20Formatting%20a%20number%20by%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1441937%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F689688%22%20target%3D%22_blank%22%3E%40Rissky1%3C%2FA%3E%26nbsp%3B%20You%20can%20do%20this%20if%20you%20create%20a%20conditional%20formatting%20rule%20and%20a%20corresponding%20custom%20number%20format%20for%20each%20currency%20format%20you%20want.%3C%2FP%3E%3CP%3ESo%20conditional%20formatting%20-%26gt%3B%20custom%20formula%20would%20be%20%3D(A3%3D%22CAN%22)%20and%20in%20the%20Format...%20you%20click%20%22Custom%22%20and%20%22Type%22%3A%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%22CAD%22%20%240%3C%2FP%3E%3CP%3ERepeat%20the%20above%20for%20each%20of%20the%20other%20currencies.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1443358%22%20slang%3D%22en-US%22%3ERe%3A%20Formatting%20a%20number%20by%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1443358%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3BTHANK%20YOU!%26nbsp%3B%20Works%20perfect.%26nbsp%3B%20Love%20this%20group%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I have a column consisting of all data validation drop downs.  Clicking on the arrow gives you 5 choices and these are all currency format option - USA, CAN, HDK etc.  Next to that column are where the user inputs values.  This is a s/sheet being used globally so that is why I have different currencies to deal with.  What I want is a conditional formatting process that will format the cell where the number is tped in based on the currency designation in the validated cells.  So, for example, cell A3 has the drop down and the user picks "CAN" because they are going to enter in data as Canadian dollars.  In Cell B3 they type in 250,000.  I want the number formatted as Canadian dollars like this - CAN 250,000.  If it was Brazil and A3 was BRL, then I want to see BRL250,000 in B3.  I don't see a way to do this in conditional formatting.  

2 Replies
Highlighted
Solution

@Rissky1  You can do this if you create a conditional formatting rule and a corresponding custom number format for each currency format you want.

So conditional formatting -> custom formula would be =(A3="CAN") and in the Format... you click "Custom" and "Type":            "CAD" $0

Repeat the above for each of the other currencies.

Highlighted

@mtarler THANK YOU!  Works perfect.  Love this group,