?
Solved

New Problem with Currency Conversion and Placement

Posted on 2014-01-21
14
Medium Priority
?
252 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 31

Expert Comment

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

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 31

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 31

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 31

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 31

Accepted Solution

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

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 31

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 31

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 Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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.
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…

770 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