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"
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.
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.