Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
recognizing the next cell and inserting the correct currency symbo
I have a cell say A1 with a drop down list of choosen currencies.eg. US $. In
the next cell say B1, I want it to insert the correct symbol depending on the currency picked to be automatically inserted. In this case $. How can I do this? Hope this is clear. Thanks Eqa. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
recognizing the next cell and inserting the correct currency symbo
Use a combination of Data Validation and VLOOKUP().
Somewhere in the worksheet create a table. The first column of the table is a textual description of the currency: Dollar Euro Mark .. .. .. Next to the first column, insert a single character for each currency symbol In A1 put the data validation pull-down In B1 put the VLOOKUP to get the symbol. -- Gary''s Student - gsnu200756 "Eqa" wrote: I have a cell say A1 with a drop down list of choosen currencies.eg. US $. In the next cell say B1, I want it to insert the correct symbol depending on the currency picked to be automatically inserted. In this case $. How can I do this? Hope this is clear. Thanks Eqa. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
recognizing the next cell and inserting the correct currency s
Gary I have just realised that I cannot enter a numeric value into the cell
without ruining the VLookup formula. How do I get around this? Thanks, Eqa "Gary''s Student" wrote: Use a combination of Data Validation and VLOOKUP(). Somewhere in the worksheet create a table. The first column of the table is a textual description of the currency: Dollar Euro Mark . . . Next to the first column, insert a single character for each currency symbol In A1 put the data validation pull-down In B1 put the VLOOKUP to get the symbol. -- Gary''s Student - gsnu200756 "Eqa" wrote: I have a cell say A1 with a drop down list of choosen currencies.eg. US $. In the next cell say B1, I want it to insert the correct symbol depending on the currency picked to be automatically inserted. In this case $. How can I do this? Hope this is clear. Thanks Eqa. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
recognizing the next cell and inserting the correct currency s
Let say rather than put a single character in B1 via VLOOKUP, we want to
format a value in B1 based upon the entry in A1. Unfortunately this cannot be accomplished easily with conditional formatting. Insert the following macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("A:A") Set t = Target If Intersect(t, r) Is Nothing Then Exit Sub v = t.Value With t.Offset(0, 1) Select Case v Case "dollar" .NumberFormat = "$#,##0.00" Case "pound" .NumberFormat = "[$£-809]#,##0.00" Case "euro" .NumberFormat = "[$‚¬-2] #,##0.00" End Select End With End Sub If you enter {pound, dollar, euro} in any cell in column A, the macro will automatically re-format the adjacent cell in column B. REMEMBER the worksheet code area, not a standard module. -- Gary''s Student - gsnu200756 "Eqa" wrote: Gary I have just realised that I cannot enter a numeric value into the cell without ruining the VLookup formula. How do I get around this? Thanks, Eqa "Gary''s Student" wrote: Use a combination of Data Validation and VLOOKUP(). Somewhere in the worksheet create a table. The first column of the table is a textual description of the currency: Dollar Euro Mark . . . Next to the first column, insert a single character for each currency symbol In A1 put the data validation pull-down In B1 put the VLOOKUP to get the symbol. -- Gary''s Student - gsnu200756 "Eqa" wrote: I have a cell say A1 with a drop down list of choosen currencies.eg. US $. In the next cell say B1, I want it to insert the correct symbol depending on the currency picked to be automatically inserted. In this case $. How can I do this? Hope this is clear. Thanks Eqa. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting Rows and Maintaining Correct Balances | Excel Discussion (Misc queries) | |||
Currency sum not correct Excel 2003 | Excel Worksheet Functions | |||
Recognizing Cell Formatting To Use In A Formula Or Conditional For | Excel Worksheet Functions | |||
Inserting another currency rather than $ on excel | Excel Worksheet Functions | |||
Keeping a sum colum correct after inserting a colum of data in fro | Excel Discussion (Misc queries) |