Currency Converter based on pick list and exchange rate

[rguliuzza]

Hi there,

I am creating a worksheet that I would like to be able to use a pick list for a country and this would be linked to an exchange rate in another range of cells. From there, I would like to have my price column to change to the new rate with the proper symbol. Anyone have a clue on how to do this? I have seen VBA on formatting the cell. I am familiar on how to change cells with a picklist; however, I have never have all these at the same time.

Any assistance would be great.

Thank you,
Rick

 

[venkat1926]

have u designed your sheets
sheet 1 should contain the plrices and the currency in different columns

sheet 2 contains that day's currency rage in matrix or in two columns(dollar to pound and pound to dollar

then use formula some vlookup and multiply by price

 

[rguliuzza]

Hi there,

Thank you, I appreciate it.

 

[venkat1926]

pos a small extsract of your sheet(s) and explain what you want to do

 

[rguliuzza]

Hi there,

Here is a snapshot of my worksheet:

[TABLE="width: 707"]
<tbody>[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD] C [/TD]
[TD] D [/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD]Pick Currency[/TD]
[TD] Yen [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD] [/TD]
[TD]Price List[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Symbol[/TD]
[TD]Exchange Rate[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Dollar (USA)[/TD]
[TD]$[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Qty[/TD]
[TD]Description[/TD]
[TD] Price [/TD]
[TD] Extended [/TD]
[TD]Pound[/TD]
[TD]£[/TD]
[TD="align: right"]0.619761[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]2[/TD]
[TD]Widget 1[/TD]
[TD] $ 10.00 [/TD]
[TD] $ 20.00 [/TD]
[TD]Euro[/TD]
[TD]€[/TD]
[TD="align: right"]0.775791[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]3[/TD]
[TD]Widget 2[/TD]
[TD] $ 15.00 [/TD]
[TD] $ 45.00 [/TD]
[TD]Yen[/TD]
[TD]¥[/TD]
[TD="align: right"]77.993[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]1[/TD]
[TD]Widget 3[/TD]
[TD] $ 20.00 [/TD]
[TD] $ 20.00 [/TD]
[TD]Yuan Renmimbi (China)[/TD]
[TD]¥[/TD]
[TD="align: right"]6.28597[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]5[/TD]
[TD]Widget 4[/TD]
[TD] $ 25.00 [/TD]
[TD] $ 125.00 [/TD]
[TD]Real (Brazil)[/TD]
[TD]R$[/TD]
[TD="align: right"]2.02619[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Dollar (Canada)[/TD]
[TD]$[/TD]
[TD="align: right"]0.982662[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD] [/TD]
[TD]Totals[/TD]
[TD] [/TD]
[TD] $ 210.00 [/TD]
[TD]Baht (Thailand)[/TD]
[TD]฿[/TD]
[TD="align: right"]30.76[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Rupee (India)[/TD]
[TD] [/TD]
[TD="align: right"]52.3788[/TD]
[/TR]
</tbody><colgroup><col><col><col><col span="2"><col><col><col></colgroup>[/TABLE]

What I am looking to do is to change the symbol and amount in column D based on the Currency Pick in column C1. I am sure there is a way to do it with VLOOKUP; however, I have yet to figure it out.

Trial and error.

Thank you,
Rick

 

[venkat1926]

put the currency symbol for yen in cell D2(A B C D etc I have taken as column headings

you cannot give formula in the same celll C7 as it will give circular reference. the formula is in H7. copy this down

the formula lfor H7 is

=D2&RIGHT(C7*VLOOKUP($D$2,F5:G12,2,0),LEN(C7*VLOOKUP($D$2,F5:G12,2,0))-1)

Sheet1

*	A	B	C	D	E	F	G	H
A	B	C	D	E	F	G	*
*	Pick Currency	Yen	¥	*	*	*	*
*	*	*	*	*	*	*	*
*	Price List	*	*	*	Symbol	Exchange Rate	*
*	*	*	*	Dollar (USA)	$	*
Qty	Description	Price	Extended	Pound	£	*
Widget 1	Euro	€	¥79.93
Widget 2	Yen	¥	¥169.895
Widget 3	Yuan Renmimbi (China)	¥	¥559.86
Widget 4	Real (Brazil)	R$	¥949.825
*	*	*	*	Dollar (Canada)	$	*
*	Totals	*	Baht (Thailand)	?	*
*	*	*	*	Rupee (India)	*	*
*	*	*	*	*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:111px;"><col style="width:163px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

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

[TD="bgcolor: #cacaca, align: center"]6[/TD]

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

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]$10.00 [/TD]
[TD="align: right"]$20.00 [/TD]

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

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]$15.00 [/TD]
[TD="align: right"]$45.00 [/TD]

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

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]1[/TD]

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

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

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]$25.00 [/TD]
[TD="align: right"]$125.00 [/TD]

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

[TD="bgcolor: #cacaca, align: center"]11[/TD]

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

[TD="bgcolor: #cacaca, align: center"]12[/TD]

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

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

[TD="bgcolor: #cacaca, align: center"]13[/TD]

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

[TD="bgcolor: #cacaca, align: center"]14[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula H7 =$D$2&RIGHT(C7*VLOOKUP($D$2,F5:G12,2,0),LEN(C7*VLOOKUP($D$2,F5:G12,2,0))-1) H8 =$D$2&RIGHT(C8*VLOOKUP($D$2,F6:G13,2,0),LEN(C8*VLOOKUP($D$2,F6:G13,2,0))-1) H9 =$D$2&RIGHT(C9*VLOOKUP($D$2,F7:G14,2,0),LEN(C9*VLOOKUP($D$2,F7:G14,2,0))-1) H10 =$D$2&RIGHT(C10*VLOOKUP($D$2,F8:G15,2,0),LEN(C10*VLOOKUP($D$2,F8:G15,2,0))-1) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[rguliuzza]

Hello VenKat,

Thank you so much.

Best regards,
RG