Solved

Currency Update and Data Placement

Posted on 2014-01-02
16
153 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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now