Solved

New Problem with Currency Conversion and Placement

Posted on 2014-01-21
14
250 Views
Last Modified: 2014-01-26
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.

Thank you in advance.

B.
Copy-of-Problem-DEMO-of-Currenc-.xlsm
0
Comment
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
  • Learn & ask questions
  • 8
  • 6
14 Comments
 
LVL 30

Expert Comment

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

Expert Comment

by:gowflow
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

by:Bright01
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 30

Expert Comment

by:gowflow
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 30

Expert Comment

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

Author Comment

by:Bright01
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 30

Expert Comment

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

Author Comment

by:Bright01
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 30

Accepted Solution

by:
gowflow earned 500 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

by:Bright01
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 30

Expert Comment

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

Author Comment

by:Bright01
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 30

Expert Comment

by:gowflow
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

by:Bright01
ID: 39810112
Gowflow,

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

b.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

707 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