Formatting Cells and Returning values based on selection from a Drop Down List in Excel

In the attached sheet, there is a Drop Down List for selecting a preferred Currency (Cell C1). The intended purpose of this sheet is to enable a person to first select his preferred currency and then provide his quotation. The person providing the quotation will only be selecting the preferred currency and filling up the Quoted Price (Column C).

In the course of this exercise, the excel sheet has to format Column C ($ format if USD is selected, € - Euro and so on) and return a value to Cell D1 from Column F (Value adjacent to each currency needs to be returned) in accordance to the selected currency from Drop Down List.

I presume the question is clear enough and thanks in advance for chipping in!!

Regards

PS: A detailed step by step solution would be highly appreciated as the real problem at hand is very complicated

Some (many?) members will not access a file-sharing site. Please attach the workbook here: click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.