+ Reply to Thread
Results 1 to 15 of 15

Controlling Currency of cells based on another cell value

  1. #1
    Registered User
    Join Date
    08-24-2014
    Location
    Swansea
    MS-Off Ver
    2016, Office 365
    Posts
    63

    Controlling Currency of cells based on another cell value

    Hi all,
    I am looking for some assistance on the best way to control Currency of selected cells based on another cell value. I have attached a worksheet as an example.

    I am looking to have the currency in cells D3:D26, E3 and H3 update when I select the currency in cell H6. I have added $ and £ but I will have a more comprehensive list in the final sheet.

    I have tried some conditional formatting with formulas but have failed miserably.

    As always all assistance, suggestions and help is very much appreciated.

    Kind regards

    Leigh
    Attached Files Attached Files

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    4,995

    Re: Controlling Currency of cells based on another cell value

    Hi,

    One option... but this code would not go in a standard module, instead in the VBA editor, double click on the sheet name that it applies to, and paste this code:

    Please Login or Register  to view this content.
    When H6 changes, the formatting will be updated.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Registered User
    Join Date
    08-24-2014
    Location
    Swansea
    MS-Off Ver
    2016, Office 365
    Posts
    63

    Re: Controlling Currency of cells based on another cell value

    Hi Arkadi,
    Many thanks for your reply, very much appreciated. The code works well; however there seems to be one bug, when selecting $ Dollars, it shows £ Pounds.

    Any suggestions to work around this would be again very appreciated.

    many thanks

    Leigh

  4. #4
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    4,995

    Re: Controlling Currency of cells based on another cell value

    Quite strange, because the symbol is being pulled straight from H6. When I try it on your sample file, I don't get that issue. Is it possible that your actual file has the drop-down in a different location? Or are you testing with the same file that you uploaded?

  5. #5
    Registered User
    Join Date
    08-24-2014
    Location
    Swansea
    MS-Off Ver
    2016, Office 365
    Posts
    63

    Re: Controlling Currency of cells based on another cell value

    Thanks for coming back, I am testing this on my final file, which does have H6 operate a list of currency options which is located on another sheet. Does this make a big difference to the code? Should I move the list to the same sheet?

  6. #6
    Registered User
    Join Date
    08-24-2014
    Location
    Swansea
    MS-Off Ver
    2016, Office 365
    Posts
    63

    Re: Controlling Currency of cells based on another cell value

    Just to update, I tried moving the list cells to the same sheet and also clearing all formats from cell H6 and just entering $ manually and the code gives me £ still lol

  7. #7
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    4,995

    Re: Controlling Currency of cells based on another cell value

    Don’t worry about changing where the list is, unless you prefer. But can you provide a file that in structure (meaning what goes where) matches your real file?
    We usually assume that in code we provide, and some references probably need fixing. So a file with a few lines, but with drop down and data where you would LIKE it to be will help a lot.

  8. #8
    Registered User
    Join Date
    08-24-2014
    Location
    Swansea
    MS-Off Ver
    2016, Office 365
    Posts
    63

    Re: Controlling Currency of cells based on another cell value

    Thanks for your continued help!!
    Ok so I have taken out all of the macros and programming and left the 3 tabs that I need the currency to change over. H6 on the first tab is where the currency drop down list is located and the tab Currency is where the list options are located.

    I'm not precious about keeping the currency in another tab so if moving it is easier, then lets do it!!
    Attached Files Attached Files

  9. #9
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,382

    Re: Controlling Currency of cells based on another cell value

    Not hard to reveal that: one person with defaul currency is USD, another is pound, with different result.
    By defaul, $ sign to tell excel that is default currency.
    with Arkdi code, try to display currency as [$$-en-us] then it should work

    HTML Code: 
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Me.Range("H6")) Is Nothing Then
        Me.Range("D3:D26").NumberFormat = "_([$" & Me.Range("H6").Value & "-en-us]* #,##0.00_);_([$" & Me.Range("H6").Value & "-en-us]* (#,##0.00);_([$" & Me.Range("H6").Value & "-en-us]* ""-""??_);_(@_)"
        Me.Range("E3").NumberFormat = "_([$" & Me.Range("H6").Value & "-en-us]* #,##0.00_);_([$" & Me.Range("H6").Value & "-en-us]* (#,##0.00);_([$" & Me.Range("H6").Value & "-en-us]* ""-""??_);_(@_)"
        Me.Range("H3").NumberFormat = "_([$" & Me.Range("H6").Value & "-en-us]* #,##0.00_);_([$" & Me.Range("H6").Value & "-en-us]* (#,##0.00);_([$" & Me.Range("H6").Value & "-en-us]* ""-""??_);_(@_)"
        Me.Range("K2").NumberFormat = "_([$" & Me.Range("H6").Value & "-en-us]* #,##0.00_);_([$" & Me.Range("H6").Value & "-en-us]* (#,##0.00);_([$" & Me.Range("H6").Value & "-en-us]* ""-""??_);_(@_)"
    End If
    End Sub

  10. #10
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    4,995

    Re: Controlling Currency of cells based on another cell value

    Nice bebo, I didn’t realize that. Thanks!

  11. #11
    Registered User
    Join Date
    08-24-2014
    Location
    Swansea
    MS-Off Ver
    2016, Office 365
    Posts
    63

    Re: Controlling Currency of cells based on another cell value

    Thanks both for your help with this, can I still use this code for the additional sheets but just add the sheet name reference for cell H6?

  12. #12
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    4,995

    Re: Controlling Currency of cells based on another cell value

    Leigh,

    The change event code needs to be on the code module for the sheet with the drop down. All other updates should change from Me. to ThisWorbook.Worksheets(“name of worksheet to update).Range….

  13. #13
    Registered User
    Join Date
    08-24-2014
    Location
    Swansea
    MS-Off Ver
    2016, Office 365
    Posts
    63

    Re: Controlling Currency of cells based on another cell value

    ahh ok got you, I will give that a go now thank you!!!

  14. #14
    Registered User
    Join Date
    08-24-2014
    Location
    Swansea
    MS-Off Ver
    2016, Office 365
    Posts
    63

    Re: Controlling Currency of cells based on another cell value

    Works like a charm now!!!! Thank you so much both, good collaborations, the help is much appreciated.

  15. #15
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    4,995

    Re: Controlling Currency of cells based on another cell value

    Glad we could help leigh3980! Thanks for the feedback, rep, and marking the thread as solved

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 19
    Last Post: 08-23-2021, 09:02 AM
  2. [SOLVED] Count cells based on formula results Dates vs Currency
    By MarcusAD79 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-20-2021, 02:18 PM
  3. Replies: 2
    Last Post: 09-26-2018, 03:19 PM
  4. Replies: 1
    Last Post: 05-30-2017, 08:03 PM
  5. Replies: 0
    Last Post: 02-18-2015, 04:42 PM
  6. multiple cells controlling one cell
    By pillory in forum Excel General
    Replies: 2
    Last Post: 05-13-2010, 11:22 AM
  7. [SOLVED] SAP BW Report - Putting the Unit of Measure or Currency in another Cell based on Format Cell
    By Frank & Pam Hayes in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-03-2005, 01:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1