Solved

Currency Conversion Integration Fix

Posted on 2014-01-13
11
222 Views
Last Modified: 2014-01-18
EE Pros,

I have a great WB and set of Macros that Gowflow has created for me and now I'm scaling it.  

I have one change so far as I integrate his work into my master WB;  I need a column of cells ("I" in Currency) to recognize the value and any changes, that are created by a formula within the cell it is converting ("H" in Currency which is linked via a formula to another cell in another WS.  Right now, I must make the change in the cell itself or put the curser at the end of the formula and click to get it to make the change.; I need to make the change in the source cell in "Demos", that should then automatically update the conversion amount.

I have attached the sample for you.

Thank you in advance.

B
Copy-of-Currency-Update-and-Disp.xlsm
0
Comment
Question by:Bright01
  • 6
  • 5
11 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39778581
Sorry but I don't understand your request at all could you explain it in a better way ?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39778607
Opps !!!! I just noticed something (I hope you haven't used an old version of the macro when you transferred the code to your production WB as)

The code in sheet Currency that I found in the attached workbook is:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns(8)) Is Nothing Then
    UpdateCurrency Target
End If
End Sub

Open in new window


Whereas the Code that I posted in the last solution that would take care of changes in Col H to reflect on Col I is for this same Currency worksheet:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns(8)) Is Nothing Or Not Intersect(Target, Columns(7)) Is Nothing Then
    UpdateCurrency Target
End If
End Sub

Open in new window


Try replacing the actual code by this last one in sheet Worksheet_change event of sheet Currency and see if it solve your issue.

gowflow
0
 

Author Comment

by:Bright01
ID: 39778627
Gowflow,  Greetings!  Just put in your change but that's not the issue.  Let me do a better job explaining.  

If you open the model I sent, you will see a new tab called "Demos".  Go to I7 or I8 and change the Amount.  You will see it did get reflected in the Currency Tab.  HOWEVER, it will not update the conversion automatically unless you put your cursor in the cell with the new data (In Currency) and hit "return".....then it updates.  What I'm attempting to have it do is automatically update from the Demo Tab when the cells are changed there, and return the new currency value (and format) to the Demo Tab in the corresponding Cells Column J.

Hope that helps.

B.
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39778719
Well now I understand your request, as did not pay attention that in Cell H5 of Currency you had a formula pointing to Demos !!

Lets recap your request, you actually want 2 things:

1) TO have the value in sheet Demo in Col J show the correct 'Value' corresponding to the formula it holds and this has been fixed in the attachment below.

2) You also want (if my understanding is correct) to also show the formatting as well of the cell this formula reference to be replicated only and strictly by the mere fact of the existence of the formula which is unfortunately not possible.
In other words, thru formula 'only' you cannot get the formatting of a cell replicated onto an other you need to use either VBA or custom format.
I thought my knowledge was limited but I did some search and found out the following:

quote

Using only formulas and no custom add-ons nor VBA: you can't. At least not with Excel up to 2007 (and quite probably all later versions as well)

First of all, there is no built-in formula that catches any form of formatting.

Second, formulas only display their values. They don't influence formatting unless you use Conditional Formatting. But even if you do, there is no easy option to "copy formatting from the other cell". Besides, conditional formatting is limited in what it can format compared with "manual mouse clicking".

Unquote

My understanding from you is that u need to replicate not only the value but also the formatting thru formula which is only possible on the value side.

Let me know
gowlfow
Copy-of-Currency-Update-and-Disp.xlsm
0
 

Author Closing Comment

by:Bright01
ID: 39778954
Gowflow!  This works very well for what I need!  Although I did say I wanted it (the currency reflected in the "Demos" tab where the formula returned the value, I don't have to have it because you have already allowed me to "reference" a cell in the "Currency" Tab and the Demos tab will be hidden anyway.

Much thanks...... you love a good challenge.  Unique talent!

B.
0
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.

 

Author Comment

by:Bright01
ID: 39778958
Can you comment on the specific line(s) you changed so I can simply refine those lines or is it more complicated and I should simply replace a section?

B.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39779076
no problem here is the new code beside the one I already included as per my last comment. So these are the codes that were not identical in the workbook you posted.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns(8)) Is Nothing Or Not Intersect(Target, Columns(7)) Is Nothing Then
    UpdateCurrency Target
End If
End Sub

Open in new window


I included this in the code of worksheet Currency. This is triggered each and every time any formula need recalculation and this is when we need to refresh the cacluation of currency module.

Private Sub Worksheet_Calculate()
Dim Target As Range
Set Target = ActiveCell
UpdateCurrency Target

End Sub

Open in new window


We tell it to simply affect the current cell (which is Demo something or any other cell and this does not matter) but what is important is that it runs the UpdateCurrency routine and that is what we are after so it simulate as if you go on the currency tab and change manually something in the cells that affect this routine.

Now as regards to the formatting I can include a code in the vba that would give you the formatting in the cell that you want in Demo but this does not mean that if you copy the formula to any cell that you will get the formatting as well. So your choice do you want me to incorporate this in the code ?

gowflow
0
 

Author Comment

by:Bright01
ID: 39780737
Gowflow,

I almost have it.  What is the specific code that updates the converted currency cells in "Demos"?   That's the only thing that isn't working in my production code.

TY,

B.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39781329
Well no code in Demos !!! that's the trick.

You should make sure to have the above code put in sheet Currency only. The most important part is the Calculate Event it is the one that triggers the update. This way if you put any reference to any other sheet in any sheet it will give you the result updated provided you have the routine set above in the calculate event. I put it again here.

Make sure you have the following cod in Currency sheet
Private Sub Worksheet_Calculate()
Dim Target As Range
Set Target = ActiveCell
UpdateCurrency Target

End Sub

Open in new window


gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39782151
Did it work for you ?
gowflow
0
 

Author Comment

by:Bright01
ID: 39790851
Gowflow,  been working on it all morning!  Have one error due to me adding a selection process in the Demos WS.

Here is the new question:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28342116.html

Thank you.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

757 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

22 Experts available now in Live!

Get 1:1 Help Now