Currency Update and Data Placement

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

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.

Any chance to have tried the proposed solution ?
gowflow

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.

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

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.

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

Just found it !!! sorry
check out this version.
gowflow
Currency-Update-and-Displayv10-m.xlsm

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

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

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.

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

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.

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

https://www.experts-exchange.com/questions/28333366/Enhancing-the-Currency-Macro.html