+ Reply to Thread
Results 1 to 11 of 11

Currency Converter based on pick list and exchange rate

  1. #1
    Registered User
    Join Date
    09-06-2012
    Location
    Santa Clara, CA
    MS-Off Ver
    Excel 2007
    Posts
    22

    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. #2
    Valued Forum Contributor ratcat's Avatar
    Join Date
    03-07-2008
    Location
    Rural NSW, Australia
    MS-Off Ver
    Vista 2007
    Posts
    1,111

    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.
    Have I made you happy ??? If yes, please make me happy by pressing the http://www.excelforum.com/images/buttons/reputation-40b.png Add Reputation button in my post.
    Please don't forget to do the same to other contributors of this forum.

    Thanks
    I don't void confusion, I create it

  3. #3
    Registered User
    Join Date
    09-06-2012
    Location
    Santa Clara, CA
    MS-Off Ver
    Excel 2007
    Posts
    22

    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. #4
    Registered User
    Join Date
    09-06-2012
    Location
    Santa Clara, CA
    MS-Off Ver
    Excel 2007
    Posts
    22

    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. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Registered User
    Join Date
    09-06-2012
    Location
    Santa Clara, CA
    MS-Off Ver
    Excel 2007
    Posts
    22

    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. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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. #8
    Registered User
    Join Date
    09-06-2012
    Location
    Santa Clara, CA
    MS-Off Ver
    Excel 2007
    Posts
    22

    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. #9
    Registered User
    Join Date
    09-06-2012
    Location
    Santa Clara, CA
    MS-Off Ver
    Excel 2007
    Posts
    22

    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. #10
    Registered User
    Join Date
    09-06-2012
    Location
    Santa Clara, CA
    MS-Off Ver
    Excel 2007
    Posts
    22

    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. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

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