Help please

[Mike]

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.

 

[Gary''s Student]

You can make a dropdown with Data Validation. See:

http://www.contextures.com/xlDataVal01.html

 

[CLR]

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]

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

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.

 

[Mike]

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

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

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.