Formatting cells Solved/Closed

Question

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

TrowaD · Answer

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.

http://speedy.sh/DayTu/Stace123-formatting-cells.xlsm

Let us know if and where you get stuck to help you further.

Best regards,
Trowa

Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.

Formatting cells

1 response

Similar discussions

Subscribe To Our Newsletter!