Change currency on all sheets based on one cell value

Hello,

I need to change currency formatting based on one selected value. So, if the 
user changes Italy as their base country, I want the currency to change to €. 
If they choose the UK, I want the currency to change to £. I have a cell 
(Inputs!A1) which updates to 1 for euro country and 2 for the UK. The cells 
ranges that need to change are Inputs!L94-M94, Inputs!L96:M98 and 
Calculations!A1:D5.

Is this doable?  

Many thinks in advance.

-- 
Johanna
0
Utf
2/5/2010 10:24:01 AM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
921 Views

Similar Articles

[PageSpeed] 6

On Feb 5, 10:24=A0am, Johanna Gronlund
<JohannaGronl...@discussions.microsoft.com> wrote:
> Hello,
>
> I need to change currency formatting based on one selected value. So, if =
the
> user changes Italy as their base country, I want the currency to change t=
o =80.
> If they choose the UK, I want the currency to change to =A3. I have a cel=
l
> (Inputs!A1) which updates to 1 for euro country and 2 for the UK. The cel=
ls
> ranges that need to change are Inputs!L94-M94, Inputs!L96:M98 and
> Calculations!A1:D5.
>
> Is this doable? =A0
>
> Many thinks in advance.
>
> --
> Johanna

Hi

You can use this code (where A1 is the cell which changes from 1 to 2
etc):-

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Address =3D "$A$1" Then
    If Cells.Range("A1").Value =3D 1 Then
        Worksheets("Inputs").Cells.Range("L94:M94").NumberFormat =3D "[$
=80-2]#,##0.00"
        Worksheets("Inputs").Cells.Range("L96:M98").NumberFormat =3D "[$
=80-2]#,##0.00"
        Worksheets("Calculations").Cells.Range("A1:D5").NumberFormat =3D
"[$=80-2]#,##0.00"
    Else
        If Cells.Range("A1").Value =3D 2 Then
            Worksheets("Inputs").Cells.Range("L94:M94").NumberFormat =3D
"=A3#,##0.00"
            Worksheets("Inputs").Cells.Range("L96:M98").NumberFormat =3D
"=A3#,##0.00"
=20
Worksheets("Calculations").Cells.Range("A1:D5").NumberFormat =3D
"=A3#,##0.00"
        End If
    End If
End If

End Sub


Hope this helps in some way.
KR Matt
http://teachr.blogspot.com
0
Matt
2/5/2010 11:26:52 AM
For some reason nothing happens if I paste this in the module 1. Is that the 
right place to paste it? Is there anything else that I might be could be 
doing wrong?

Thanks!

-- 
Johanna


"Matt Richardson" wrote:

> On Feb 5, 10:24 am, Johanna Gronlund
> <JohannaGronl...@discussions.microsoft.com> wrote:
> > Hello,
> >
> > I need to change currency formatting based on one selected value. So, if the
> > user changes Italy as their base country, I want the currency to change to €.
> > If they choose the UK, I want the currency to change to £. I have a cell
> > (Inputs!A1) which updates to 1 for euro country and 2 for the UK. The cells
> > ranges that need to change are Inputs!L94-M94, Inputs!L96:M98 and
> > Calculations!A1:D5.
> >
> > Is this doable?  
> >
> > Many thinks in advance.
> >
> > --
> > Johanna
> 
> Hi
> 
> You can use this code (where A1 is the cell which changes from 1 to 2
> etc):-
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
> 
> 
> If Target.Address = "$A$1" Then
>     If Cells.Range("A1").Value = 1 Then
>         Worksheets("Inputs").Cells.Range("L94:M94").NumberFormat = "[$
> €-2]#,##0.00"
>         Worksheets("Inputs").Cells.Range("L96:M98").NumberFormat = "[$
> €-2]#,##0.00"
>         Worksheets("Calculations").Cells.Range("A1:D5").NumberFormat =
> "[$€-2]#,##0.00"
>     Else
>         If Cells.Range("A1").Value = 2 Then
>             Worksheets("Inputs").Cells.Range("L94:M94").NumberFormat =
> "£#,##0.00"
>             Worksheets("Inputs").Cells.Range("L96:M98").NumberFormat =
> "£#,##0.00"
>  
> Worksheets("Calculations").Cells.Range("A1:D5").NumberFormat =
> "£#,##0.00"
>         End If
>     End If
> End If
> 
> End Sub
> 
> 
> Hope this helps in some way.
> KR Matt
> http://teachr.blogspot.com
> .
> 
0
Utf
2/5/2010 2:20:01 PM
On Feb 5, 2:20=A0pm, Johanna Gronlund
<JohannaGronl...@discussions.microsoft.com> wrote:
> For some reason nothing happens if I paste this in the module 1. Is that =
the
> right place to paste it? Is there anything else that I might be could be
> doing wrong?
>
> Thanks!
>
> --
> Johanna
>
>
>
> "Matt Richardson" wrote:
> > On Feb 5, 10:24 am, Johanna Gronlund
> > <JohannaGronl...@discussions.microsoft.com> wrote:
> > > Hello,
>
> > > I need to change currency formatting based on one selected value. So,=
 if the
> > > user changes Italy as their base country, I want the currency to chan=
ge to =80.
> > > If they choose the UK, I want the currency to change to =A3. I have a=
 cell
> > > (Inputs!A1) which updates to 1 for euro country and 2 for the UK. The=
 cells
> > > ranges that need to change are Inputs!L94-M94, Inputs!L96:M98 and
> > > Calculations!A1:D5.
>
> > > Is this doable? =A0
>
> > > Many thinks in advance.
>
> > > --
> > > Johanna
>
> > Hi
>
> > You can use this code (where A1 is the cell which changes from 1 to 2
> > etc):-
>
> > Private Sub Worksheet_Change(ByVal Target As Range)
>
> > If Target.Address =3D "$A$1" Then
> > =A0 =A0 If Cells.Range("A1").Value =3D 1 Then
> > =A0 =A0 =A0 =A0 Worksheets("Inputs").Cells.Range("L94:M94").NumberForma=
t =3D "[$
> > =80-2]#,##0.00"
> > =A0 =A0 =A0 =A0 Worksheets("Inputs").Cells.Range("L96:M98").NumberForma=
t =3D "[$
> > =80-2]#,##0.00"
> > =A0 =A0 =A0 =A0 Worksheets("Calculations").Cells.Range("A1:D5").NumberF=
ormat =3D
> > "[$=80-2]#,##0.00"
> > =A0 =A0 Else
> > =A0 =A0 =A0 =A0 If Cells.Range("A1").Value =3D 2 Then
> > =A0 =A0 =A0 =A0 =A0 =A0 Worksheets("Inputs").Cells.Range("L94:M94").Num=
berFormat =3D
> > "=A3#,##0.00"
> > =A0 =A0 =A0 =A0 =A0 =A0 Worksheets("Inputs").Cells.Range("L96:M98").Num=
berFormat =3D
> > "=A3#,##0.00"
>
> > Worksheets("Calculations").Cells.Range("A1:D5").NumberFormat =3D
> > "=A3#,##0.00"
> > =A0 =A0 =A0 =A0 End If
> > =A0 =A0 End If
> > End If
>
> > End Sub
>
> > Hope this helps in some way.
> > KR Matt
> >http://teachr.blogspot.com
> > .

Paste this in the module of the worksheet that you want to change.  It
should work then.

Matt
http://teachr.blogspot.com
0
Matt
2/11/2010 3:18:37 PM
Reply:

Similar Artilces:

How to sort multiple sheets (which have same format) at one time?
I have a workbook with multiple sheets which have the same format. I need to sort all in the same manner. How can this be done. I noticed that the sort function is not available when selecting/grouping multiple sheets. I'm going to take you at your word and make a few assumptions. Taking you at your word: ALL sheets in the workbook need to be sorted. Assumptions: only 1 column is used to determine the sort order, and the sort is to be in ascending order. The following code will do that, and allows you to define which columns are included in the sort, and which column ...

exporting/linking data from a row on one sheet into another sheet
I have a master sheet with all the sales information that my company tracks. I would like to create seperate sheets for each sales rep. I have all their initials listed under one column. Can I take each row and put the information onto another sheet based on what I put into the initials colum? exporting/linking data from a row on one sheet into another sheet Hi Clark see responses in Excel.worksheet.functions NG Frank Clark Haddock wrote: > I have a master sheet with all the sales information that > my company tracks. I would like to create seperate > sheets for each sa...

Right Click to Autofill cells in 2003
Hello All, In 97 to 2002 it was possible to right click when using Autofill to copy the cells down rather than continuing the sequence. in 2003 i just get a pretty red line (what purpose does this serve?). Does anyone know if I can get back the old functionality? Many thanks, Danny I have that functionality in Excel 2003 as well, maybe you have installed an add-in? In any case if you hold down ctrl while using the left click copy down it will also copy as opposed to fill a series Regards, peo sjoblom "DannyJ" wrote: > Hello All, > > In 97 to 2002 it was possi...

With Microsoft FAX Nearly One Minute Delay between Using Print
With Windows Messaging and Microsoft Fax under Win 98 SE, when I printed a document to Microsoft Fax, the fax wizard would start a few seconds after I said print. I installed Outlook 2000. With it, there seems to be a consistent nearly one minute delay between when I say print and the fax wizard starts. If Outlook is running, there is no delay. The osa9.exe program doesn't make any difference. Needless to say, this is an annoyance, having to go through two steps, start Outlook and then print, rather than simply printing. Does anyone know of a way to make Outlook 2000 work like Messag...

How do I randomly shuffle the cells in a column?
In Excel 2003, I have a column, say a1-a10, with certain entries. I need to create another column, b1-b10, with the same entries assigned randomly (shuffled) to the cells. Just one way .. Put in B1: =INDEX(A:A,RANK(C1,$C$1:$C$10)) Put in C1: =RAND() Select B1:C1, copy down to C10 B1:B10 returns a random shuffle of what's in A1:A10 Press F9 to re-shuffle -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Jason D" <Jason D@discussions.microsoft.com> wrote in message news:36244977-7EE5-436B-8617-59C15310B080@microsoft.com... >...

Change Contract Status
Hello, My CRM implementation does not change automatically the Contract Status from Active to Expired or from Invoiced to Active when the Contract End Date or, respectively, the Contract Start Date comes. This was happening also in the 1.2 version and now the same happens in 3.0 version. Any ideas? Thank you, Elena ...

How to change cell shading/fill based on value in another cell?
I have a sheet with two columns of experimental data. Let's say the data is in F10:F25 (column 1) & G10:G25 (column 2). There is a value in another cell (A1) that contains a 1 or a 2. If A1=1, I want to turn shading in column 1 agree and in column 2 off. If A1=2, I want to reverse that. If there a worksheet function that will do that? I was hoping for something like: =cellshading(range,color) I could then execute one of these two statements: If A1=1 then cellshading(F10:F25,"Green") cellshading(G10:G25,"None") Else cellshading(G10:G25,"Green&...

Outlook 2003 Font Changes When Replying Plain Text
Outlook 2003 is exhibiting a strange behavior when replying to plain text messages. After clicking the "Reply" button, the message appears in the Arial font. In about two to three minutes, the font suddenly changes to Courier which is the correct font. Why is it taking so long for the font to change? Steve Lockridge steve@websitewarehouse.com ...

How to get SUMPRODUCT on filtered cells
I'm currently using the formula =SUMPRODUCT((F2:I1475>=1)*(F2:I1475<=9999)) This gives me the number of the full range of cells but I need this to update whenever I use a variety of filters. Any help is greatly appreciated Sam.D Say we have data in A1 thru B29 like: flia value 0 pass 2 pass 1 pass 2 pass 1 pass 2 pass 0 pass 2 pass 1 pass 2 pass 1 pass 0 pass 2 pass 0 pass 1 pass 1 fail 1 fail 0 fail 2 fail 1 fail 0 fail 1 fail 2 fail 1 fail 1 fail 1 fail 1 fail 2 fail and are appling autofilter to column a. We want to use sumpr...

Sum Multiple Worksheets, same named cell
I have multiple worksheets containing the same named cell (e.g. Month_Total). On a totals worksheet, I am not able to use =SUM('START,END'!Month_Total) because of the local/global naming. Is there a way around this? (Month_Total will not always be in the same cell on each worksheet). Thanks! It seems to me that you have two options. 1. Name each of your "Monthly Total"s slightly different. OR 2. Do not use the cell range naming. "uncreative" <uncreative@discussions.microsoft.com> wrote in message news:60A150C6-50BC-4876-A31F-1C...

Visio2k7 has two PDF export methods
I have a floorplan diagram that is 20"x20". On the diagram, there is metadata for the shapes of the offices that tells you the person's name, phone #, etc. When I use the "Publish as PDF or XPS", the Image is exported properly, as shown in the link below: http://img527.imageshack.us/img527/2676/error1kn6.jpg But none of the metadata comes across - and there's no option to include it either that I can find. However, if you use the "Convert to Adobe PDF" option, you get the opposite: http://img503.imageshack.us/img503/2893/error2ou8.jpg The i...

Data sheet populating into multiple sheets
I'm looking to build a spreadsheet with one main page that can be populated with data and automatically pull that data over to other more specific sheets. For example, If I have sales on the main sheet and have check boxes for more specific sales on the same sheet (i.e. liquor sales, food sales etc..) I want that number to poulate on the specific sales sheet. I'm assumming I have to wite an IF/Then Statement to say if an "x" is put into a cell then copy and past the data in the specified cells within that row and pull that information over to sheet Y. One respon...

charting a single bar graph on one line showing continuous info
I must do a time graph showing total workhours with tick marks showing begin and end of tours on a dialy basis. I cannot get the custom or standard chart graph functions to do this without putting in all of the data. I just want the begin tour and end tour of each person. can you help. thx debi ...

how can you change a "bitmap" to a jpeg??
After taking digital pictures in BITMAP, how can I change them to JPEGS? "TRM" <TRM@discussions.microsoft.com> wrote in message news:F5C091D7-377B-4F2F-A7BF-84794BC84F38@microsoft.com... > After taking digital pictures in BITMAP, how can I change them to > JPEGS? Get XnView or IrfanView. Both are free. Then use Save As and select the format. -- _________________________________________________________________ ******** Post replies to newsgroup - Share with others ******** Email: lh_811newsATyahooDOTcom and append "=NEWS=" to Subject. _________________...

Algebra within a cell
How do you set-up a formula in a cell that multiplies a constant times the number you insert? Ex. the constant is .315 remains present at all times only the number you insert changes - =.315*(x) You can't unless you use an event macro, if you need a formula you have to use another cell as help =0.315*A2 where A2 holds x you can also put 0.315 in a cell, copy it, select the cel with x and then paste special and select multiply. But to get this instantly you have to use something like Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Range("A2"), Target)...

sbs 2008 under Updates, "change the software update settings" crashes console
Running SBS 2008 SP2, fresh install with just a couple users, I noticed that under Security, Updates, in the Tasks window there is a "Change the software update settings". When I click this link, the "Software Update Settings" window appears for a split second then the SBS Console crashes saying "Windows SBS 2008 Console has stopped working" with these details: Description: Stopped working Problem signature: Problem Event Name: CLR20r3 Problem Signature 01: console.exe Problem Signature 02: 6.0.5601.8497 Problem Signature 03: 4a612b8b...

age-based life insurance
Hello: There's not way for the GP HR Life Insurance Setup window to calculate age-based insurance as of January 1 instead of by a person's age, is there? Thanks! childofthe1980s I would like to know this too. TD "childothe1980s" wrote: > Hello: > > There's not way for the GP HR Life Insurance Setup window to calculate > age-based insurance as of January 1 instead of by a person's age, is there? > > Thanks! > > childofthe1980s ...

Displaying Multiple Cell Information in Single Cell
Hi all, I'm trying to present (text) data from multiple cells in another workbook in a single cell. I've tried the following formula but it returns a #VALUE! in my destination cell: ='[Project1.xls]Dependencies'!$A$6, '[Project1.xls]Dependencies'!$A$7 Any ideas on if this is possible? TIA, SamuelT -- SamuelT ------------------------------------------------------------------------ SamuelT's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27501 View this thread: http://www.excelforum.com/showthread.php?threadid=503954 Use the "&...

knowlege base article 289815
How can i download this fix q289815.exe to solve the following problem Visio2000: Cross-Functional Flowchart Shapes Incorrectly Aligned When You Open Drawing Efe, The bottom of that article indicates that you need to contact PSS: "To resolve this problem, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information about support costs, visit the following Microsoft Web site: http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS" -- Dawn This posting is provided "AS IS&qu...

Formatting cells
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel All of a sudden when I try to format a column of or an individual cell (format as a number, no decimal places, comma for thousands) only 2 of the four numbers appear in the cell. When I double click to view what is actually in the cell I see the four numbers I entered, the first two separated by the last two with a decimal. <br> I have tried this on new sheets and workbooks and the same thing happens. I can't tell for sure, but try going to Excel>Preferences> Edit. If there's a check on '...

How do I add multiple comments to a cell?
I need to add more than one comment to data in a single cell, is this possible? Not possible. deberjones wrote: > > I need to add more than one comment to data in a single cell, is this possible? -- Dave Peterson ...

make excel files with same names to be opened at one time
It is very difficult to work if you want to compare changed data made in the file with old one ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=cde27763-1bc7-4299-874f-ac53e066ae7a&dg=microsoft.public....

Can I e-mail only one worksheet in a workbook using MSN Hotmail?
...

Multiple accounts:one bank (different IDs & pswd)
How do i set up auto-online updates when u have multiple accounts at one bank w/ different IDs and pswds? In microsoft.public.money, garnd wrote: >How do i set up auto-online updates when u have multiple accounts at one bank >w/ different IDs and pswds? What program are you using? MM 05 "Cal Learner-- MVP" wrote: > In microsoft.public.money, garnd wrote: > > >How do i set up auto-online updates when u have multiple accounts at one bank > >w/ different IDs and pswds? > > What program are you using? > > In microsoft.public.money, garnd...

Sheets Tabs
Is there a way to change the location of the sheets tabs? Instead of having them at the bottom of the worksheet, I would need to have them vertically, next to the row number. I know it is kind of a weird question, but it would simplify my job :-) Thanks in advance. Regards, Emece.- NO -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Emece" <Emece@discussions.microsoft.com> wrote in message news:DF6FDE2F-6513-44CB-ADE4-99F57A8CE7E9@microsoft.com... > Is there a way to change the location of the sheets tabs? Instead of >...