Excel Currency Conversion

EE Pros,

Greetings!  I have a simple spreadsheet and what I need is a currency converter that will automatically convert from one currency to another. The Spreadsheet is attached and has a simple dropdown box for the selection.  Key, but not mandatory, is to this is the ability to have the currencies stay relatively current through some web service.

Thank you in advance,

B.
Currency-Conversion.xlsm
Bright01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

J NUnicorn wranglerCommented:
0
Bright01Author Commented:
Jay,

Thanks for the quick reply; I watched the video but what I'm looking for is a way to automate the conversion in such a manner so that end users can select their currency and see immediate results.  Can you provide some context in the file I sent so that I can see exactly how it may work?

Thank you again,

B.
0
Steven HarrisPresidentCommented:
Here is a working copy of the link jaymenagy posted.

The workbook has an active Data Connection to MSN Money, and uses VLOOKUP.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Bright01Author Commented:
ThinkSpaceSolutions,

Can you attach the file?

Thank you,

B.
0
Steven HarrisPresidentCommented:
I apologize, I noticed an error in the formula and removed the attachment.
Here is a working copy.

Please note, this is based off of the link  jaymenagy posted and will need to be revised to get into your prefered format of Selectable Currency to Selectable Currency.
Foreign-Currency-Converter.xlsx
0
Bright01Author Commented:
ThinkSpace and Jay,

I couldn't get this to work.  All I need is to be able to do is convert dollars into another currency, not vice versa.  It's a one way trip.

Also, is there a macro that can automatically refresh the currency by firing off the Macro?

Thank you,

B.
0
Steven HarrisPresidentCommented:
Apparently I uploaded the corrupted workbook again.  Sorry for the hassles.

I have created an entirely new one for you that will:

...convert dollars into another currency, not vice versa.

This Data Connection will refresh each time the workbook is open, and then every 30 minutes thereafter.  You can change these settings under Data > Connections.  Select the Connection and Click Properties.

To use:  Change D5 to the currency you want to convert to and change the US$ value in cell E4.
US-to-Foreign-Currency.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
byundtMechanical EngineerCommented:
In ThinkSpaceSolutions' workbook, I believe there was a typo in the VLOOKUP formula. It should have used the values in lookup table column C, not B.
=VLOOKUP(D5 & "*",'MSN Money Data'!$A$5:$C$46,3,FALSE)*E4

I also thought it might look better if the dropdown didn't keep saying " - US Dollar" after the foreign currency, so I split the URL with:
=IFERROR(LEFT(A5,FIND("-",A5)-2),A5)            
I then put the resulting values in a named range called Currencies

The reason for the & "*" as the first parameter of VLOOKUP in the first formula is so the foreign currency would match the lookup table without need for the " - US Dollar"

The posted file is ThinkSpaceSolutions' file with the above small changes.
US-to-Foreign-CurrencyQ28243312.xlsx
0
Bright01Author Commented:
Great work guys!  This is definitely something I can use!  Also, I'll be asking a similar question around how do I take a workbook and allow for currency change within the workbook itself. It's more complicated but you've given me a great start here.

Thank you very much for the efforts!

All the best,

B.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.