converting currencies using exchange rate

Hi all!

In my sheet, lets say from cell C to F, there would be values in different currencies i.e. EURO, US and GBP but i want the end result to be always in US. For example, i want to know if its possible that if someone puts a value in EURO, it should lookup in a cell which would have EURUSD exchange rate and convert the inserted EUR value into US. The same should happen for GBP.

FYI, if a value is in EUR or GBP, the cell is formatted manualy to display EUR or GBP sign.

I came across the below thread while searching the fourms and it looks similar to mine but i don't understand it all as i am a real noob in excel.

http://www.excelforum.com/excel-gene...cy-symbol.html


I hope my explanation is clear enough.

Can you upload example?

attached is a sample!

Let me make an another attempt in explaining my need.

I have currency rates in C2 and C3 as:

EURUSD
GBPUSD

I will have users entering values from C10 below till F10 below. Most of these values would be in USD but some of them would be GBP and EUR and all these cells would be formatted to show the currency symbols. If someone enters a value in USD then of course no conversion would happen but if someone enters in EUR then it should be converted based on the value available in C2. Similarly, it should look C3 for GBP conversion.

For example if i enter a value in EUR in cell C11 then the value entered should automatically be converted to USD based on the value in C2.

Hope this is clear!

Thanks

anything guys ?

If VBA is the only option for this solution then i think i don't have any other choice but to go for it.

Only 2 users would be updating the sheet and the rest would only be viewing it.

And i am aware of the risk of differentiating currencies but i will manage it.

Thanks!

Where the user enters the amounts, could you not have a cell to the left of it validated to accept GBP or EUR and then based on whether one or the other is selected, run a nested IF statement to calculate the exchange? For example, if User A inputted "500" into cell C10 and it was a EUR value, could B10 not be validated to choose EUR and then D10 use a formula to say =IF(B10="EUR", SUM(C10*C2), IF(B10="GBP",SUM(C10*D2),"")) - obviously, this way would leave you with 2 entries; the original 500 in cell C10 entered by the user, plus another calculated entry based on the exchange rate.

jamieleeuk, thats another option i am looking at it and have implemented it in my attached sheet but have below questions and probs:

- what if the CR is USD only as in my formula now i have:

=IF(C10="EUR", SUM(D10*D2), IF(C10="GBP",SUM(D10*D3),""))


- If you see in my sheet, in some cells i have text like INCL instead of nos. What would happen to the formula in that case?

- my sum formula in column O doesn't seem to be working for some reason now :S

- Is it not possible to do all the conversion in column O on the basis of CR columns? Like if CR is EUR or GBP or USD in each cell value of a row then it does the necessary calculation/conversion and the VLookup thing?

anything guys pls ?