[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

# New Problem with Currency Conversion and Placement

Posted on 2014-01-21
Medium Priority
256 Views
EE Pros,

I have a WB that Gowflow has built for me that works to specification and works great.  The problem is that I didn't give him the right design specifications for part of the WB and as I integrated it, I found a problem.

The problem is that I have two different Reference values; one in Currency WS and one in Demos WS.  The one in Currency should reference placing currency related values back to the Demos WS and then the Demos WS should then place the new converted values to very specific locations in other Worksheets.  That's it.

Attached is a file to show you the challenge.

B.
Copy-of-Problem-DEMO-of-Currenc-.xlsm
0
Question by:Bright01
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 8
• 6

LVL 31

Expert Comment

ID: 39797357
Will attend upon back from tennis ! :)
gowflow
0

LVL 31

Expert Comment

ID: 39798008
I looked at all the comments but I am a bit lost.

What is required ?? comment in sheet Customer-Input ?
gowlfow
0

Author Comment

ID: 39798080
Let's start from the beginning.....

1.) On Customer-Inputs you select a currency (F2)
2.) On Demos, a condition is selected (Conservative, Standard, Aggressive) which puts a USD\$ Value (in I), from the selection, in a specific cell in the Currency WS (H) with a formula.  -- This works
3.) The Currency WS Macro converts the USD\$ (H) to the selected Currency (I) and then, based on the "Reference Cell" (Currency WS (G)), it places the converted value back into the Demos WS (J). - This Works
4.) From the Demos WS, the new converted amount (I) is placed where the "Reference Cell" tells it to be placed on other Worksheets (D). - This does not work.

Where I messed up was not realizing that I had designed two "Reference Cells" and never gave you the reason as to what they were for.

Very sorry.

B.
Copy-of-Problem-DEMO-of-Currenc-.xlsm
0

LVL 31

Expert Comment

ID: 39799327
Well here it is. I have moved things around for you, test it and if all is ok let me know so I tell you exactly what you should do to update your production workbook (trying to avoid you waste of time) !!! :)

I just noticed I didn't mention what this version will do. It will do more than what you requested not only when you update demos that it will update the figures in the appropriate sheets, but also if you select a new currency all items will reflect in all sheets correctly.

One Last thing remains which is the cleanup of previous values when you change reference, this I prefer you post a clean new question only to take care of this as it is lengthy as now you have 2 subs. You will see now a new Sub UpdateDemos in Module2 and I removed all blabla from Worksheet_Change event you should always make the habit to keep these worksheet events to the minimum possible code as they are called very often and you may run into situation that you can have several loops and loose track and get wrong data updated.

gowflow
Problem-DEMO-of-Currenc-V06.xlsm
0

LVL 31

Expert Comment

ID: 39802242
Any chance to have checked it out ?
gowflow
0

Author Comment

ID: 39803450
Sorry for the delay.....had to put down our 19 year old dog yesterday...rough day.

Let me test it now....thank you for your patience and efforts.

b.
0

LVL 31

Expert Comment

ID: 39803465
I am deeply sorry. Sure tough. Take your time.
gowflow
0

Author Comment

ID: 39803474
Gowflow,

This is correct except, I need to insure two things  (only #1 doesn't work);

1.) when you change the selection in the Demos WS, it needs to be reflected in the Customer Input WS (you will see that you have to re-select the Currency to get the update.

and

2.) because it is a "demo", there will be times when the real data is entered into the cells in customer input and they will have nothing to do with the macros.  I tried it and it works like that now.  I just wanted to make sure that a background macro wouldn't affect the model when used beyond the demo.
0

LVL 31

Accepted Solution

gowflow earned 2000 total points
ID: 39803587
Yes for 1  my mistake. Here it is

For 2 not clear I do not understand.

gowflow
Problem-DEMO-of-Currenc-V07.xlsm
0

Author Closing Comment

ID: 39803611
Excellent.  Thank you very much!

As for #2, I was just hedging my bets that if when I integrate this code and if I have a problem, I will not look so stupid as to not mention it earlier ;-)

Thanks again.  I'll be back with you soon.

B.
0

LVL 31

Expert Comment

ID: 39803623
ok glad I could help. Anytime pls
gowflow
0

Author Comment

ID: 39808075
Gowflow,

I have a question for you.  I'm trying to move the code you made for me into my production code.  Here's my question.  How do I best do it?  I've tried several ways but it fails to work.

1.) Should I copy over the Macros first? Then adapt the Tabs to fit the Macros?
or
2.) Should I copy the tabs over then copy Macros over?

Any advice?  If I need your help, can I hire you to help me?  if so, how do I get in touch with you?

Thanks for all your help.  You have been great.

B.
0

LVL 31

Expert Comment

ID: 39808307
If you would like dedicated work I am ready and can offer that as well but you need to understand that this is done outside EE and cannot be mixed with questions in here.

Experts are allowed to offer paid services and by checking their profile you can get in contact directly, however an Expert cannot take advantage of outside communications to leverage it out on questions in here as all Experts should have equal chances and be on equal ground when it comes to asking questions in EE.

This being said, If you are interested in this avenue pls check my profile it has my credentials there.
gowflow
0

Author Comment

ID: 39810112
Gowflow,

I sent you a note to your email account.  Please take a look.

b.
0

## Featured Post

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference texâ€¦
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templaâ€¦
###### Suggested Courses
Course of the Month12 days, 17 hours left to enroll