Currency Converter based on pick list and exchange rate

1. Currency Converter based on pick list and exchange rate

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

2. Re: Currency Converter based on pick list and exchange rate

G'day Rick,

For us to help you, the large majority of contributors of this forum don't build spreadsheets from scratch. So I'm quite sure if you supply a dummy workbook that represents the real workbook of yours, its possible that I or other contributors may come to your aid.

3. Re: Currency Converter based on pick list and exchange rate

Hi There,

Thank you for your reply. I appreciate it. I will see if I can make a dummy spreadsheet and attached it.

Best regards,
Rick

4. Re: Currency Converter based on pick list and exchange rate

Hi there,

Here is the code I am using to try and get the results to have only two decimal places:

=VLOOKUP(\$D\$2,G5:I13,2,FALSE)&" "& ROUND(D7*(VLOOKUP(\$D\$2,G5:I13,3,FALSE)),3)

The first VLOOKUP command is finding the correct Currency Symbol while the Second VLOOKUP is performing the calculation on the price based on current currency rates. When I use this or the TRUNC command, I still cannot get the results to end in 2 decimal places.

Any clues?

Thank you,
RG

5. Re: Currency Converter based on pick list and exchange rate

as suggested, for us to help you, it would be far better for you to upload a sample workbook. i have a feeling that the way you are doing this, you are ending up with a text answer, rather than a value (currency) answer.

we await your sample workbook

6. Re: Currency Converter based on pick list and exchange rate

Hi there,

This is interesting. When I add the Currency Symbol to the equation, it loses the round function. i.e.:

=VLOOKUP(\$D\$2,\$H\$6:\$I\$13,2,FALSE)&" "&IF(\$D\$2="USA Dollar",1,(ROUND(D7*(VLOOKUP(\$D\$2&" to US Dollar",\$T\$5:\$V\$46,3,FALSE)),2)))

This will return the symbol with the converted amount; however, there are times when the amount will come back:

¥ 782.3 instead of ¥ 782.30

Any ideas???

Thank you,
RG

7. Re: Currency Converter based on pick list and exchange rate

i doubt any1 else will offer any more suggestions, you have been asked twice to provide a sample workbook

8. Re: Currency Converter based on pick list and exchange rate

CurrencyConversion.xlsm

Hi there,

Not sure if this worked, but here is the workbook. It is a little messy right now as I have been experimenting. I am focused on Cell F7.

Thank you,
RG

9. Re: Currency Converter based on pick list and exchange rate

HI FDibbins,

Thank you for the reminder. I included the worksheet on another post this morning.

I appreciate your assistance.

Best regards,
RG

10. Re: Currency Converter based on pick list and exchange rate

Hi there,

I am thinking that the fact that I am combining a string with a numeral is creating a string in the cell and turning my calculation into a string. I tried to do the calculation in another cell and just add it to the end of my first VLOOKUP and it changed the number from 782.30 in cell F17 to 782.3 in the formula cell. i.e.:

=VLOOKUP(\$D\$2,\$H\$6:\$I\$13,2,FALSE)&" "&ROUND( F17,2)

Any ideas?

Thank you,
Rick

11. Re: Currency Converter based on pick list and exchange rate

as i said before...
``Please Login or Register  to view this content.``
if all you want to do is add the symbol to the value, try this...
=IF( \$D\$2 ="USA Dollar", 1, VLOOKUP(\$D\$2,\$H\$6:\$I\$13,2,FALSE)&" "&(VLOOKUP(\$D\$2 & " to US Dollar",\$T\$5:\$V\$46,3,FALSE))*D7)

if later, you want to use that result in a formula elsewhere, refer to it using this...
=MID(F18,FIND(" ",F18,1),6)*1

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1