Formatting cells

Stace123 Posts 3 Registration date Saturday July 25, 2015 Status Member Last seen July 25, 2015 - Jul 25, 2015 at 04:46 AM
 Stace123 - Jul 28, 2015 at 08:41 PM
I'm stuck.... I am creating an invoice of sorts for a company who deals in different currencies.
Q1. I have created a drop down list of all products available. Once the product is chosen from the list (say in B4) I want a corresponding cell to choose the cost of the product from a list then insert the value of this product automatically (into say C4). Any help for a quick fix?
The answer to Question 1 is the main question as 2 is based on having the one invoice that will cover a number of currencies. It would probably be easier to have separate invoices for each currency/country but just in case....

Q2. Following on from the previous question, is there a way if a cell (Say A1) shows a particular country/location, ie Australia, USA, Europe, then the currency for the row (Say Row C) will automatically choose the correct currency? Say if USA is chosen from the drop down menu then Column C will know that it has to choose the cost of each product as in B4 from a list in US dollars.

Hopefully I have made some sense in my question. Cheers

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jul 28, 2015 at 11:44 AM
Hi Stace,

Q1: Linking drop down lists.
1st step would be to create a list of products and there prices.
Create a named range (the field left of the formula bar that normally displays the cell currently selected) for each product.
Now in C4 we create a drop down list referring to the named range by using the formula: =INDIRECT(B4)

Q2: Using VBA to change number format.
After implementing the following code the format of C4 will change according to the country you chose in A1:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
If Target.Value = "USA" Then Range("C4").NumberFormat = "[$$-409]#,##0.00"
If Target.Value = "Australia" Then Range("C4").NumberFormat = "[$€-413] #,##0.00"
If Target.Value = "Europe" Then Range("C4").NumberFormat = "[$€-413] #,##0.00"
End Sub

As you can see the format for Australia and Europe is the same as I couldn't find Australia in the list.
To use the format you want, record a macro > change the format > stop recording > paste the format from recorded macro to the above provided macro. This way you can add as many countries as you want.

According to your knowledge of Excel this can be quit a handful. The link below has a workbook which has all the above implemented to further assist you. It also contains a recorded macro (Module 1) so you can see how that would look like. The above code is located under Sheet1.

Let us know if and where you get stuck to help you further.

Best regards,

Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
Thank you so much for the answer. As you have clearly explained what I need I think I can work through it! I have done a fair bit in excel some years ago so I should be OK but I now know where to come to ask a question.