Need help - currency signs & groups

[greenbirdredbeak2015]

PLEASE SEE MY ATTACHED SPREADSHEET; IT HAS NOTES...I WANT TO MULTIPLY 2 CELLS AND HAVE THE RESULT POPULATE IN THE CORRECT FORM OF CURRENCY (DOLLARS, EUROS & POUNDS). I ALSO NEE TO GROUP COLUMNS, HAVE THE NAME APPEAR ABOVE THE GROUP AND DUPLICATE IT FOR EACH MONTH IN THE REST OF 2015.

THAN KS!
MICHELLE[TABLE="width: 1145"]
<colgroup><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD]Vendor[/TD]
[TD]Due Date[/TD]
[TD]Recurring[/TD]
[TD]Currency[/TD]
[TD]Exchange Rate to USD[/TD]
[TD]estimate cost in original currency[/TD]
[TD]estimate cost in dollars[/TD]
[TD]actual cost in original currency[/TD]
[TD]actual cost in us dollars[/TD]
[/TR]
[TR]
[TD]Godaddy[/TD]
[TD="align: right"]5/15/2015[/TD]
[TD]monthly[/TD]
[TD]USD[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD] $ 25.00[/TD]
[TD][/TD]
[TD] $ 28.00[/TD]
[/TR]
[TR]
[TD]Internet[/TD]
[TD="align: right"]6/1/2015[/TD]
[TD]monthly[/TD]
[TD]EURO[/TD]
[TD="align: right"]0.84[/TD]
[TD][/TD]
[TD] $ 100.00[/TD]
[TD][/TD]
[TD] $ 90.00[/TD]
[/TR]
[TR]
[TD]Hosting[/TD]
[TD="align: right"]7/1/2015[/TD]
[TD]monthly[/TD]
[TD]GBP[/TD]
[TD="align: right"]0.69[/TD]
[TD][/TD]
[TD] $ 80.00[/TD]
[TD][/TD]
[TD] $ 40.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]I want this column to multiply estimate in dollars times exchange rate AND have the result in the correct form of currency (using the currency sign)[/TD]
[TD][/TD]
[TD]I want this column to multiply actual cost in dollars times exchange rate AND have the result in the correct form of currency (using the currency sign)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]*I also want to group columns F-I and name them "July" and duplicate them for each month in the rest 2015. [/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

You need to do three things: 1) set up a lookup table that contains the currency exchange rates (while ensuring you and the users understand the direction of the exchange rate), and 2) set up one Conditional Formatting rule for each currency exchange rate pair you have (you have three), 3) avoid typing in all-caps.

The CondFrmt rules in my exemplar here would be as follows: in cell F2, execute Conditional Formatting| NewRule| Use a formula...|type in the If True box =$D2=$D$8|Format|Number|Currency|Symbol|select dollar sign|click OK all the way out; still in cell F2, execute Conditional Formatting| NewRule| Use a formula...|type in the If True box =$D2=$D$9|Format|Number|Currency|Symbol|select one of the euro symbols in the scrolly bar|click OK all the way out; and still in cell F2, execute Conditional Formatting| NewRule| Use a formula...|type in the If True box =$D2=$D$10|Format|Number|Currency|Symbol|select one of the Pound symbols in the scrolly bar|click OK all the way out. Copy F2 and paste the formatting (and just the formatting!) into F3,F4,H2,H3 and H4.

Note that this method will become daunting and unwieldy if you have a large list of currency pairs. I think VBA code might be required to have Excel refer to an array for the Conditional Formatting.

Excel 2012
	A	B	C	D	E	F	G	H	I
1	Vendor	Due Date	Recurrance	Currency		estimated cost in original currency	estimated cost (USD)	actual cost in original currency	actual cost (USD)
2	Godaddy	5/15/2015	monthly	USD		$25.00	25	$28.00	28
3	Internet	6/1/2015	monthly	EURO		€ 84.00	100	€ 75.60	90
4	Hosting	7/1/2015	monthly	GBP		£55.20	80	£27.60	40
5
6				Currency Table
7				Currency	Exchange Rate (USD buys)	format (fyi)
8				USD	1.0000	$#,##0.00
9				EURO	0.8400	[$€-2] #,##0.00
10				GBP	0.6900	[$£-809]#,##0.00
Sheet37

Cell Formulas
Range	Formula
F2	=VLOOKUP(D2,$D$8:$E$10,2,0)*G2
F3	=VLOOKUP(D3,$D$8:$E$10,2,0)*G3
F4	=VLOOKUP(D4,$D$8:$E$10,2,0)*G4
H2	=VLOOKUP(D2,$D$8:$E$10,2,0)*I2
H3	=VLOOKUP(D3,$D$8:$E$10,2,0)*I3
H4	=VLOOKUP(D4,$D$8:$E$10,2,0)*I4

 

[DRSteele]

If you had a long list of currency exchange rates, you could use a lookup table like this one here. It avoids the need of creating conditional formatting rules. Unfortunately, though, it turns the figures into text; but one could simply use the NUMBERVALUE function if he wanted to do math on those numbers.

Excel 2012
	A	B	C	D	E	F	G	H	I	J
15	Vendor	Due Date	Recurrance	Currency		estimated cost in original currency	estimated cost (USD)	actual cost in original currency	actual cost (USD)
16	Godaddy	5/15/2015	monthly	USD		$25.00	25	$28.00	28	25
17	Internet	6/1/2015	monthly	EURO		€84.00	100	€75.60	90
18	Hosting	7/1/2015	monthly	GBP		£55.20	80	£27.60	40
19	test	8/1/2015	monthly	JPY		¥9,995.52	81	¥5,059.46	41
20
21				Currency Table
22				Currency	Exchange Rate (USD buys)	format (codes)
23				USD	1.0000	$#,##0.00
24				EURO	0.8400	[$€-2]#,##0.00
25				GBP	0.6900	[$£-809]#,##0.00
26				AUD	1.292324	$#,##0.00
27				JPY	123.4015	[$¥-411]#,##0.00
28				CAD	1.2304	$#,##0.00
29				CHF	0.9278	[$CHF] #,##0.00
30				et cetera
Sheet37

Cell Formulas
Range	Formula
J16	=NUMBERVALUE(F16)
F16	=TEXT(VLOOKUP($D16,$D$23:$E$29,2,0)*G16,VLOOKUP($D16,$D$23:$F$29,3,0))
F17	=TEXT(VLOOKUP($D17,$D$23:$E$29,2,0)*G17,VLOOKUP($D17,$D$23:$F$29,3,0))
F18	=TEXT(VLOOKUP($D18,$D$23:$E$29,2,0)*G18,VLOOKUP($D18,$D$23:$F$29,3,0))
F19	=TEXT(VLOOKUP($D19,$D$23:$E$29,2,0)*G19,VLOOKUP($D19,$D$23:$F$29,3,0))
H16	=TEXT(VLOOKUP($D16,$D$23:$E$29,2,0)*I16,VLOOKUP($D16,$D$23:$F$29,3,0))
H17	=TEXT(VLOOKUP($D17,$D$23:$E$29,2,0)*I17,VLOOKUP($D17,$D$23:$F$29,3,0))
H18	=TEXT(VLOOKUP($D18,$D$23:$E$29,2,0)*I18,VLOOKUP($D18,$D$23:$F$29,3,0))
H19	=TEXT(VLOOKUP($D19,$D$23:$E$29,2,0)*I19,VLOOKUP($D19,$D$23:$F$29,3,0))

 

[greenbirdredbeak2015]

Here is my exact spreadsheet, can someone please help me write the formulas/ conditional formatting? I need Cell I5 (Estimate/Vendor 1) to look at G5 and go to the worksheet "Currency" and determine which currency sign to enter in I5, and multiply K5&H5. Thanks!!!!!!!!!!



Here is my exact spreadsheet called "Estimate vs Actual Costs", can someone please write out the formulas for me?

[TABLE="width: 1688"]
<colgroup><col><col><col><col span="4"><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Service Provider[/TD]
[TD]Type of Service[/TD]
[TD]Due Date[/TD]
[TD]Recurring[/TD]
[TD]Set-up Fee[/TD]
[TD]Monthly Minimum[/TD]
[TD]Country[/TD]
[TD]Currency Conversion Rate as of 6/11/14[/TD]
[TD]Currency[/TD]
[TD]Estimate Cost in Original Currency[/TD]
[TD]Estimate Cost in US Dollars[/TD]
[TD]Actual Cost in Original Type of Currency[/TD]
[TD]Actual Cost
in US Dollars[/TD]
[/TR]
[TR]
[TD]Vendor 1[/TD]
[TD]Software[/TD]
[TD]6/10/2015[/TD]
[TD]Annually[/TD]
[TD][/TD]
[TD][/TD]
[TD]USD[/TD]
[TD]1.00[/TD]
[TD]USD[/TD]
[TD][/TD]
[TD] $ 25.00[/TD]
[TD][/TD]
[TD] $ 28.00[/TD]
[/TR]
[TR]
[TD]Vendor 2[/TD]
[TD]Software[/TD]
[TD]7/15/2015[/TD]
[TD]Monthly[/TD]
[TD][/TD]
[TD][/TD]
[TD]EURO[/TD]
[TD]0.84[/TD]
[TD]EURO[/TD]
[TD][/TD]
[TD] $ 100.00[/TD]
[TD][/TD]
[TD] $ 90.00[/TD]
[/TR]
[TR]
[TD]Vendor 3[/TD]
[TD]Software[/TD]
[TD]7/1/2015[/TD]
[TD]Monthly[/TD]
[TD][/TD]
[TD][/TD]
[TD]GBP[/TD]
[TD]0.69[/TD]
[TD]GBP[/TD]
[TD][/TD]
[TD] $ 80.00[/TD]
[TD][/TD]
[TD] $ 40.00[/TD]
[/TR]
</tbody>[/TABLE]



and in the second tab called "Currency" I have this:

[TABLE="width: 288"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Country[/TD]
[TD]ExchRate (USD Buys)[/TD]
[/TR]
[TR]
[TD]USD[/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD]EURO[/TD]
[TD]0.84[/TD]
[/TR]
[TR]
[TD]GBP[/TD]
[TD]0.69

[/TD]
[/TR]
</tbody>[/TABLE]

If you had a long list of currency exchange rates, you could use a lookup table like this one here. It avoids the need of creating conditional formatting rules. Unfortunately, though, it turns the figures into text; but one could simply use the NUMBERVALUE function if he wanted to do math on those numbers.

Excel 2012

	A	B	C	D	E	F	G	H	I	J
Vendor	Due Date	Recurrance	Currency	estimated cost in original currency	estimated cost (USD)	actual cost in original currency	actual cost (USD)
Godaddy	monthly	USD
Internet	monthly	EURO
Hosting	monthly	GBP
test	monthly	JPY
Currency Table
Currency	Exchange Rate (USD buys)	format (codes)
USD	$#,##0.00
EURO	[$€-2]#,##0.00
GBP	[$£-809]#,##0.00
AUD	$#,##0.00
JPY	[$¥-411]#,##0.00
CAD	$#,##0.00
CHF	[$CHF] #,##0.00
et cetera

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]15[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]5/15/2015[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #FFE699"]$25.00[/TD]
[TD="align: right"]25[/TD]
[TD="bgcolor: #FFE699"]$28.00[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]25[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]6/1/2015[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #FFE699"]€84.00[/TD]
[TD="align: right"]100[/TD]
[TD="bgcolor: #FFE699"]€75.60[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]7/1/2015[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #FFE699"]£55.20[/TD]
[TD="align: right"]80[/TD]
[TD="bgcolor: #FFE699"]£27.60[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]8/1/2015[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #FFE699"]¥9,995.52[/TD]
[TD="align: right"]81[/TD]
[TD="bgcolor: #FFE699"]¥5,059.46[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1.0000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.8400[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.6900[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1.292324[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]123.4015[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1.2304[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.9278[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet37​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J16[/TH]
[TD="align: left"]=NUMBERVALUE(F16)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F16[/TH]
[TD="align: left"]=TEXT(VLOOKUP($D16,$D$23:$E$29,2,0)*G16,VLOOKUP($D16,$D$23:$F$29,3,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F17[/TH]
[TD="align: left"]=TEXT(VLOOKUP($D17,$D$23:$E$29,2,0)*G17,VLOOKUP($D17,$D$23:$F$29,3,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F18[/TH]
[TD="align: left"]=TEXT(VLOOKUP($D18,$D$23:$E$29,2,0)*G18,VLOOKUP($D18,$D$23:$F$29,3,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F19[/TH]
[TD="align: left"]=TEXT(VLOOKUP($D19,$D$23:$E$29,2,0)*G19,VLOOKUP($D19,$D$23:$F$29,3,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H16[/TH]
[TD="align: left"]=TEXT(VLOOKUP($D16,$D$23:$E$29,2,0)*I16,VLOOKUP($D16,$D$23:$F$29,3,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H17[/TH]
[TD="align: left"]=TEXT(VLOOKUP($D17,$D$23:$E$29,2,0)*I17,VLOOKUP($D17,$D$23:$F$29,3,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H18[/TH]
[TD="align: left"]=TEXT(VLOOKUP($D18,$D$23:$E$29,2,0)*I18,VLOOKUP($D18,$D$23:$F$29,3,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H19[/TH]
[TD="align: left"]=TEXT(VLOOKUP($D19,$D$23:$E$29,2,0)*I19,VLOOKUP($D19,$D$23:$F$29,3,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]