Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help please
Im trying to create an invoice template with an option to change currencies,
via a drop down list - only two values, USD or GBP Once the user selects either USD or GBP, the spreadsheet will then show USD [or GBP] in the unit price and sub-total columns and it will also alter bank details [sort code, account number, swift code] further down the template. I've got it working using IF commands but I have to type in either USD or GBP, whereas I'd prefer to select it from a list. Thanks in advance. I'm using a slightly amended copy of the - Sales invoice with tax and shipping and handling calculations template - from the microsoft website. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help please
You can make a dropdown with Data Validation. See:
http://www.contextures.com/xlDataVal01.html -- Gary''s Student - gsnu200767 "Mike" wrote: Im trying to create an invoice template with an option to change currencies, via a drop down list - only two values, USD or GBP Once the user selects either USD or GBP, the spreadsheet will then show USD [or GBP] in the unit price and sub-total columns and it will also alter bank details [sort code, account number, swift code] further down the template. I've got it working using IF commands but I have to type in either USD or GBP, whereas I'd prefer to select it from a list. Thanks in advance. I'm using a slightly amended copy of the - Sales invoice with tax and shipping and handling calculations template - from the microsoft website. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help please
With this macro, the cell will change from USD to GBP or back each time you
click on the cell......... Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If Target.Value = "USD" Then Target.Value = "GBP" Else If Target.Value = "GBP" Then Target.Value = "USD" End If End If End Sub Vaya con Dios, Chuck, CABGx3 "Mike" wrote: Im trying to create an invoice template with an option to change currencies, via a drop down list - only two values, USD or GBP Once the user selects either USD or GBP, the spreadsheet will then show USD [or GBP] in the unit price and sub-total columns and it will also alter bank details [sort code, account number, swift code] further down the template. I've got it working using IF commands but I have to type in either USD or GBP, whereas I'd prefer to select it from a list. Thanks in advance. I'm using a slightly amended copy of the - Sales invoice with tax and shipping and handling calculations template - from the microsoft website. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help please
Thanks Gary student - i've had a look at the website but to be honest - its
beyond me. I'll have another go at explaining what I need. Imagine an invoice with a cell that allows the user to select either USD or GBP as the currency for that particular invoice - with the the USD $ or GBP £ being entered for every line item of the invoice, the subtotal showing the result of a calculation - again with the corresponding prefix and finally the total for the invoice as a result of a SUM function - again with the proper prefix depending upon the users choice. All this is good, but the final twist - the chosen currency cell needs to alter SORT, SWIFT, ACCOUNT and IBAN numbers to reflect details dependent upon whether its the USD or the GBP account. It's difficult to explain but I hope you can see what I need? If not, check out the single rate tax calculation invoice [infopath version] to get a visual on the currency selection cell and combine that function in the modified Sales invoice with tax and shipping and handling calculations template that I am using. Phew! Thanks for all your help so far You can make a dropdown with Data Validation. See: http://www.contextures.com/xlDataVal01.html -- Gary''s Student - gsnu200767 "Mike" wrote: Im trying to create an invoice template with an option to change currencies, via a drop down list - only two values, USD or GBP Once the user selects either USD or GBP, the spreadsheet will then show USD [or GBP] in the unit price and sub-total columns and it will also alter bank details [sort code, account number, swift code] further down the template. I've got it working using IF commands but I have to type in either USD or GBP, whereas I'd prefer to select it from a list. Thanks in advance. I'm using a slightly amended copy of the - Sales invoice with tax and shipping and handling calculations template - from the microsoft website. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help please
Thanks for the advice CLR but to be honest - I havent a clue how to do any of
that or where to type it even! I'll have another go at explaining what I need. Imagine an invoice with a cell that allows the user to select either USD or GBP as the currency for that particular invoice - with the the USD $ or GBP £ being entered for every line item of the invoice, the subtotal showing the result of a calculation - again with the corresponding prefix and finally the total for the invoice as a result of a SUM function - again with the proper prefix depending upon the users choice. All this is good, but the final twist - the chosen currency cell needs to alter SORT, SWIFT, ACCOUNT and IBAN numbers to reflect details dependent upon whether its the USD or the GBP account. It's difficult to explain but I hope you can see what I need? If not, check out the single rate tax calculation invoice [infopath version] to get a visual on the currency selection cell and combine that function in the modified Sales invoice with tax and shipping and handling calculations template that I am using. Phew! Thanks for all your help so far "CLR" wrote: With this macro, the cell will change from USD to GBP or back each time you click on the cell......... Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If Target.Value = "USD" Then Target.Value = "GBP" Else If Target.Value = "GBP" Then Target.Value = "USD" End If End If End Sub Vaya con Dios, Chuck, CABGx3 "Mike" wrote: Im trying to create an invoice template with an option to change currencies, via a drop down list - only two values, USD or GBP Once the user selects either USD or GBP, the spreadsheet will then show USD [or GBP] in the unit price and sub-total columns and it will also alter bank details [sort code, account number, swift code] further down the template. I've got it working using IF commands but I have to type in either USD or GBP, whereas I'd prefer to select it from a list. Thanks in advance. I'm using a slightly amended copy of the - Sales invoice with tax and shipping and handling calculations template - from the microsoft website. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|