Solved

Currency Update and Data Placement

Posted on 2014-01-02
16
155 Views
Last Modified: 2014-01-08
EE Pros,

I have a workbook where I need to have a Macro that assists in Currency Conversion and data placement.  The attached WB illustrates the Macro flow (Red Numbers).

When selecting the Currency in WS1, the proper Currency Calculation is made in WS3 against a Currency Table.  The result is then placed in specified cells.

That's it!

Thank you in advance.
Currency-Update-and-Displayv10.xlsm
0
Comment
Question by:Bright01
  • 9
  • 7
16 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39753303
what is requested ? its not clear ! I see the flow 1, 2, 3 4 ... but what do you need from us to develop ? I see in sheet Currency from Col U to the right you have a dump of currency conversion values is this done manually ? you need to update ? what is needed ???

IO recall having worked on a similar stuff for currency in the past but do not see any of the code in module you posted.

Pls explain it in plain English
gowflow
0
 

Author Comment

by:Bright01
ID: 39756019
Gowflow,

I'm sorry for this not being clear.  Let me see if this makes it easier and more clear;

1.) You select a "Currency" on WS1 ("Customer-Input").  The macro sees the selection and

2.) performs a calculation on WS3 ("Currency").  The calculation is to take the USD value in Column H and apply the conversion table to yield its equivalency in Column I in the selected Currency.

3.) Then.... it places the value that has just been calculated, in Column I, in the Cell referenced in G ("Currency")  where it is shown in the proper currency and value according to the conversion from USD to the selected Currency.

Does that make sense?

Thank you,

B.
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39757640
ok I hope this is what you want.

1) I added all the Currencies and changed the Existing ones to match your data currency table so the lookup will always find the data. The list is now in Currency sheet cell B4 to B46. You can have fun to lookup for the missing symbols but the currency exchange works.

2)  You noticed I changed in the table United States Dollar to US Dollars the before last item and each time you update the table you will need to make sure that it is written this way so it is not confused with the existing United States Dollar that exist for each and every currency.

3) I added a dropdown in Cell F2 so you may lookup the currency easily instead of simply typing it.

4) You can sort all the currencies in Sheet Currency cells B4 to B46 but left it as is as presume the first 12 are the ones you need. (Other doesn't make sense).

Check the whole thing out and let me know your comments.
Rgds/gowflow
Currency-Update-and-Displayv10-m.xlsm
0
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 29

Expert Comment

by:gowflow
ID: 39761535
Any chance to have tried the proposed solution ?
gowflow
0
 

Author Closing Comment

by:Bright01
ID: 39762412
Outstanding!  Thank you Gowflow..... very well done and quick.  You really know how to take a very complex scenario and take it apart in order to build a great function/macro.

Really appreciate it.  And hope to build on it with another question soon.

Thanks again,

B.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39762456
Thank you for your very nice and rewarding comment. Pls feel free to post in here any link to any question you may need help with.
Rgds/gowflow
0
 

Author Comment

by:Bright01
ID: 39762915
Gowflow,

Thanks for the offer!  I have found one math problem and one macro problem with the code and cannot figure out how you do the calculation (where in the code....)....otherwise I'd try to fix this.

1.) Try selecting China Yuan.  The dollar conversion is $1USD to 6 Yuan.  I think you have the math backwards.

2.) The macro needs to know when the USD Amount/value changes in "Currency" Column H.  So to say it another way, if I have a formula/reference that updates any of the cells in column H, it should update the converted values and repost to the location references.  Currently, you need to update the USD Amount in Column H and then rerun the Currency selection in order to get the new values.

If you like, I can post this as an additional question.

Thank you,

 b.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39763367
I am not sure I understood what is the problem. I use only the first column in the table Col W as the example you posted was dollar amount and its counter value in the currency you select.

Are you saying that you put now amount in a certain currency and what the counter value in dollar ??

If you want maybe better to post a new question and put a link here and I will look at all scenarios.

If the macro I wrote have a fault then no need for a new question I will fix it. If it is additional feature you want then a new question should be.

Pls advise.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39763392
Just found it !!! sorry
check out this version.
gowflow
Currency-Update-and-Displayv10-m.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39764823
Did you have a chance to see the update file ? any comment as to the Dollar issue ? is anything fixed with this version ?
gowflow
0
 

Author Comment

by:Bright01
ID: 39764939
Gowflow,

Yes....the math is now completely correct.  

The other part of this was that when a value changes in terms of a change in USD (column H in "Currency"), it automatically updates the model in terms of any/all conversion values (column i in "Currency") and reflects those changes in all destination cells.  I think this is as simple as recognizing a change and auto refreshing the macro.

Does that make sense?   I've changed the illustration so you can see what I'm talking about in the attached model.

B
Copy-of-Currency-Update-and-Disp.xlsm
0
 

Author Comment

by:Bright01
ID: 39764947
Gowflow,

Let me also use an example.

Open the WB.  Select Yuan as the currency.  When you do that, the value in I, in "Currency" will read 6093845.21 Yuan.  This is correct.  Now go to the USD value of $1,000,000 in Column H in Currency.  Change it to $500,000....nothing happens.  This is where, the value in the Conversion Currency (Yuan) should read 3046922.60 and reflect that new value in the referenced cells.  In other words, a change in USD base value should re-run the math to convert the value into the selected currency.

Hope this helps!

B.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39765174
Well now I understand what you want !!!!

But this was not part of the initial posting and fear that we need to move things around to achieve what you want.

Technically speaking, the macro (that was developed and I adapted to your need) reside in the worksheet_change event of sheet Customer-Inputs therefore it react at changes in this sheet only ! this is why it did not react to changes in an other sheet.

Now you say when any item in sheet currency or col I is changed you need this macro to react well then we need to change the whole concept and put the whole thing differently so you can achieve this.

I propose (not for points) but for the principle that you post a new question and simply ask for what you just specified to happen and will be glad to move things around for you. You can then compare the two and see you will endup with something (technically speacking) totally different from what you have.

I will ask you to please be precise so we know where the changes could happen ie
could it also happen in other sheets ? price Quote if yes what cells ? if in Currency then also how many lines or columns ? I need all this as we are talking about a different ball altogether not just simply 'refresh' as you may think

Just for you to know the routine was acting ONLY when Cell F2 in sheet cutomer-Inputs was affected or changed and this instruction is not me who put it !

So now we need to move things around and remove the whole routine from where it is and put it in a module with for sure different passing variables reason why I say it is a different ball game.

gowflow
0
 

Author Comment

by:Bright01
ID: 39765219
Fair enough; I'll post another Question to enhance the model.  I have no problem with that.  This is a complex macro or set of macros and I think you are right..... I need to build on it with separate questions.  

Your first macro was excellent and did most of what I needed .... I didn't realize until testing it beyond a quick look that it didn't update when I changed the USD amount.  So I didn't intentionally mislead you.

I'll post another question and a sincere "thank you" for the great work you have already done.  

B.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39765319
no problem It is already working in here !!! I love challenges just post a link in here and will be glad to give it to you
gowflow
0
 

Author Comment

by:Bright01
ID: 39765358
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

805 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question