Solved

New Problem with Currency Conversion and Placement

Posted on 2014-01-21
14
213 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
  • 8
  • 6
14 Comments
 
LVL 29

Expert Comment

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

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
 
LVL 29

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 29

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 29

Expert Comment

by:gowflow
ID: 39803465
I am deeply sorry. Sure tough. Take your time.
gowflow
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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 29

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 29

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 29

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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 …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
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.

758 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

18 Experts available now in Live!

Get 1:1 Help Now