Change currency symbol for the entire worksheet with a drop-down currency list

vishukm86

New Member
Joined
Dec 24, 2019
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Can someone pls suggest me how to apply a currency symbol to an entire worksheet with a range of numbers just by adding and choosing a currency type from a dropdown list.

Eg.: I have a dropdown list in a Cell "C2" for USD, AED, EUR, INR, BDT & NPR. When I select a specific currency from the drop-down list in Cell "C2", all the numbers in the worksheet has to be prefixed with the corresponding currency symbol.

Link to the file : Comparision Statement Automated Pilot.xlsx
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

hernantorres23

Active Member
Joined
Nov 21, 2019
Messages
269
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Web
Hi, it is very dificult put this file here, based in your layout.
This code work when you change the value in C5 (currency dropdown list located in sheet "Price Comparision")

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQ
1
2
3ProjectXYZRFRFQ ID
4Date25/12/2019Percent Savings w.r.t. LCP (Without freight & tax)15.29%27.75%3.33%8.51%43.29%27.75%
5Ordering CurrencyINRPercent Savings w.r.t. Budget (Without freight & tax)15.29%27.75%3.33%8.52%43.30%27.75%
6Conversion Factor (if applicable)Total amount of individual vendors₹ 1,003,000.0₹ 854,960.01,144,600.00 1,082,700.00 671,420.00 854,960.00
7Lowest Bid671,420.00GUID
8L1 VendorELEKTROLITES (POWER) PVT.LTDVendor Codes910091019102910391049105
9Vendor State
10Vendor NamesAIP INDIA LIMITEDCG POWERELECTROTHERM INDIA LIMITEDADITYA BIRLA INSULATORSELEKTROLITES (POWER) PVT.LTDVOLTAMP TRANSFORMERS LTD
11Material/Service Desc.Model No.QTYUOMMDFLCPBIOBUDGET COSTTARGET COSTGST %PR No.HSN/SACUNIT PRICE (R0)UNIT PRICE (R1)UNIT PRICE (R2)TAX CODETOTAL PRICEUNIT PRICE (R0)UNIT PRICE (R1)UNIT PRICE (R2)TAX CODETOTAL PRICEUNIT PRICE (R0)UNIT PRICE (R1)UNIT PRICE (R2)TAX CODETOTAL PRICEUNIT PRICE (R0)UNIT PRICE (R1)UNIT PRICE (R2)TAX CODETOTAL PRICEUNIT PRICE (R0)UNIT PRICE (R1)UNIT PRICE (R2)TAX CODETOTAL PRICEUNIT PRICE (R0)UNIT PRICE (R1)UNIT PRICE (R2)TAX CODETOTAL PRICE
12245kV, 2500A Isolators100₹ 1,003,356.0₹ 1,106,356.0₹ 1,003,455.0₹ 1,003,455.018%₹ 8,670.0₹ 8,670.0₹ 8,500.0₹ 850,000.0₹ 8,000.0₹ 7,500.0₹ 7,220.0₹ 722,000.0৳ 9,900.0৳ 9,800.0৳ 9,700.0৳ 970,000.0৳ 9,870.0৳ 9,200.0৳ 9,150.0৳ 915,000.0৳ 5,690.0৳ 5,690.0৳ 5,690.0৳ 569,000.0৳ 8,000.0৳ 7,500.0৳ 7,220.0৳ 722,000.0
1310₹ 52.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 500.0₹ 600.0₹ 300.0₹ 3,000.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 500.0৳ 600.0৳ 300.0৳ 3,000.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 500.0৳ 600.0৳ 300.0৳ 3,000.0
14₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0
15₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0
16₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0
17₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0
18₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0
19₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0
20₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0
21₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0
22₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0
23₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0
24₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0
25₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0
26₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0
27₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0
28₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0
29₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0
30₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0₹ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0
31Total₹ 1,003,408.0₹ 1,106,356.0₹ 1,003,455.0₹ 1,003,455.0 SUBTOTAL₹ 850,000.0₹ 725,000.0৳ 970,000.0৳ 918,000.0৳ 569,000.0৳ 725,000.0
32GSTTOTAL TAX₹ 153,000.0₹ 129,960.0৳ 174,600.0৳ 164,700.0৳ 102,420.0৳ 129,960.0
33Shipping/Freight costSHIPPING₹ 0.0₹ 0.0৳ 0.0৳ 0.0৳ 0.0৳ 0.0
34TOTAL₹ 1,003,000.0₹ 854,960.0৳ 1,144,600.0৳ 1,082,700.0৳ 671,420.0৳ 854,960.0
35
36Incoterms
37Payment Terms
38LD %
39PBG %
40ABG %
41Warranty Period
42Defect Liability Period
43
Price Comparision
Cell Formulas
RangeFormula
N4N4=(G31-R31)/G31
S4S4=(G31-W31)/G31
X4X4=(G31-AB31)/G31
AC4AC4=(G31-AG31)/G31
AH4AH4=(G31-AL31)/G31
AM4AM4=(G31-AQ31)/G31
N5N5=(I31-R31)/I31
S5S5=((I31-W31)/I31)*100%
X5X5=((I31-AB31)/I31*100%)
AC5AC5=((I31-AG31)/I31)*100%
AH5AH5=((I31-AL31)/I31)*100%
AM5AM5=((I31-AQ31)/I31)*100%
N6, S6, X6, AC6, AH6, AM6N6=R34
C7C7=MIN(N6:AQ6)
C8C8=INDEX($N$10:$AQ$10,MATCH(C7,N6:AQ6,0))
G31:J31, AQ31, AL31, AG31, AB31, W31, R31G31=SUM(G12:G30)
R12:R30R12=(MIN(N12:P12)*D12)
R32R32=SUMPRODUCT(K12:K30,R12:R30)
W12:W30W12=(MIN(S12:U12)*D12)
W32W32=SUMPRODUCT(K12:K30,W12:W30)
AB12:AB30AB12=(MIN(X12:Z12)*D12)
AB32AB32=SUMPRODUCT(K12:K30,AB12:AB30)
AG12:AG30AG12=(MIN(AC12:AE12)*D12)
AG32AG32=SUMPRODUCT(K12:K30,AG12:AG30)
AL12:AL30AL12=(MIN(AH12:AJ12)*D12)
AL32AL32=SUMPRODUCT(K12:K30,AL12:AL30)
AQ12:AQ30AQ12=(MIN(AM12:AO12)*D12)
AQ32AQ32=SUMPRODUCT(K12:K30,AQ12:AQ30)
R34, AQ34, AL34, AG34, AB34, W34R34=SUM(R31,R32,R33)
Named Ranges
NameRefers ToCells
'Price Comparision'!_FilterDatabase='Price Comparision'!$B$10:$AQ$10C8:H8
'Price Comparision'!Print_Area='Price Comparision'!$B$1:$AQ$34N4:AQ6, C7:H8, G31:J31, R12:R32, W12:W32, AB12:AB32, AG12:AG32, AL12:AL32, AQ12:AQ32, R34, W34, AB34, AG34, AL34, AQ34
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AM6:AO7,AH6:AJ7,AC6:AE7,X6:Z7,N6:O6,S6:U7Other TypeColor scaleNO
AM6:AO7,AH6:AJ7,AC6:AE7,X6:Z7,N6:O6,S6:U7Other TypeColor scaleNO
N5:AQ5Other TypeColor scaleNO
N5:AQ5Other TypeColor scaleNO
N5:AQ5Other TypeColor scaleNO
N4:AQ4Other TypeColor scaleNO
N4:AQ4Other TypeColor scaleNO
N4:AQ4Other TypeColor scaleNO
N4:AQ4Other TypeColor scaleNO
Cells with Data Validation
CellAllowCriteria
C5:H5List=Currency


Book1
ABCDEFGHIJKL
1
2CurrencySymbolCompanies
3INRAIP INDIA LIMITEDChange symbols like you want
4BDTCG POWER
5NPRELECTROTHERM INDIA LIMITED
6USD$ADITYA BIRLA INSULATORSThis is an Excel table (Ctrl + t) named "parameters" without quotes
7EURELEKTROLITES (POWER) PVT.LTD
8VOLTAMP TRANSFORMERS LTD
9
10
Do not disturb


Code for Worksheet("Price Comparision")
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Dim myCurrSel As String
Set KeyCells = Range("C5")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
FormatCells (KeyCells)
End If
End Sub

Code in standar module
VBA Code:
Option Explicit
'by Hernan Torres (Mikel ERP)
'December 25, 2019
'Refer to several sheets
'Question by vishukm86
'topic: https://www.mrexcel.com/board/threads/change-currency-symbol-for-the-entire-worksheet-with-a-drop-down-currency-list.1118810/


Sub FormatCells(currSelected As String)
Const skipRows As Long = 11 'for skip eleven rows
Dim b(), c(), myCurrFormat   'b for layout, c for companies
Dim pinCell As Range
Dim l As Long, h As Long, i As Long, j As Long, k As Long 'l=low limit row; h=high limit row; j=jumps between company ranges; k=qty companies
Dim myCurrency As String, rngValues As String, rngTotal As String, rngVendors As String

Application.ScreenUpdating = False
myCurrency = Application.Index(Range("parameters"), Application.Match(currSelected, Range("parameters[Currency]"), 0), 2)

myCurrFormat = "\" & myCurrency & "  #,##0.#0;\" & myCurrency & " -#,##0.#0"
Sheets("Do not disturb").Activate
c = Sheets("Do not disturb").Range("D3:D" & Range("D" & Rows.Count).End(xlUp).Row).Value2
k = UBound(c)
Sheets("Price Comparision").Activate
b = Sheets("Price Comparision").Range("G12:AQ" & Range("G" & Rows.Count).End(xlUp).Row).Value2
l = LBound(b) + skipRows
h = UBound(b) + skipRows
Range(Cells(l, 7), Cells(h, 10)).Select
Selection.NumberFormat = myCurrFormat
Set pinCell = Cells(l, 11).Offset(0, 3)

For i = 1 To UBound(c) 'for each company
rngValues = Range(Cells(l, pinCell.Column + j), Cells(h, pinCell.Offset(0, 2).Column + j)).Address(RowAbsolute:=False, ColumnAbsolute:=False, ReferenceStyle:=xlA1)
rngTotal = Range(Cells(l, pinCell.Offset(0, j + 4).Column), Cells(h + 3, pinCell.Offset(0, j + 4).Column)).Address(RowAbsolute:=False, ColumnAbsolute:=False, ReferenceStyle:=xlA1)
rngVendors = Range(Cells(6, pinCell.Column + j).Address).Address(RowAbsolute:=False, ColumnAbsolute:=False, ReferenceStyle:=xlA1) 'total for each vendors
Range(rngValues & "," & rngTotal & "," & rngVendors).Select
Selection.NumberFormat = myCurrFormat
'Debug.Print c(i, 1)
j = j + 5

Next i
Application.ScreenUpdating = True
Range("I3").Activate
End Sub

Here, you can download the full example
 

Watch MrExcel Video

Forum statistics

Threads
1,123,416
Messages
5,601,550
Members
414,458
Latest member
mekhallet salah

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top