Excel 2007 : Convert sales into a currency corresponding from a drop down

[anstasija]

Hi Everyone,

I am struggling with creating a drop down menu (working in Excel2007) that converts the sales values into the selected currency from the drop down menu, adding the currency symbol (like $).

I have been looking at other post on this site and "googled" other websites but somehow cannot find anything that is understandable to me as I am not a VBA programmer.

Basically, what I need is that the values in the Sales column (red) are converted to the currency that can be selected from the drop down menu (Display Currency). The Sales column is a calculation based on price, currency and volume.

I know how to create the drop down (Data, Data Validation, Allow: List, Source: =$C$1:$C$4) but cannot create the "link" between the data and the drop down.

I took a look at another file my company is using with this function and somehow the drop down source listes fields that include formulas like "=VALUE(CONCATENATE(A11,4))".

Hope I was not too complicated in my description. I have included an "exercise file".

Grateful appreciate your help!!!

[Marcol]

Try this workbook.

Create two named ranges, "Currency" and "CurrencyTable"

Use the name "Currency" for the Data Validation in C8 down

In D8

Please Login or Register  to view this content.

Drag/Fill Down

In G8

Please Login or Register  to view this content.

Drag/Fill Down
This will return the cost/price in Euros.

Is that what you are trying to do?

[anstasija]

Thank you very much. It is almost what I need to do but kind of opposite direction.

The selection of currency needs to be in the top area (like in cell A2).

The cells in rows 7 to 10 should not have any drop downs.

When A2 drop down gets changed from current Euro to USD or other currency only the numbers in G8-G10 will change and show the leading currency symbol. I believe that having the currency symbol is more a "nice to have".

I cannot even tell you how big of a help it would be. I have spent several hours today playing around and trying to find it out...

Thanks!