Help converting currencies

[irishshopper]

I receive a sales report every month from eBay re my eBay store as an excel spreadsheet.

The Sales Total column on the excel spreadsheet can be in one of 5 currencies £, Euro, US $, CAD $ or AUS $.

I want to put in the next column the value shown but in GBP so I can then total this colum to get the grand total in GBP.

I also need to split the spreadshhet into two halves one for orders from EU and work out the total and same for non EU.

I cannot use the colums the totals are in to calculate anything as the column is set to GENERAL format and the prices have US$, Euro as a prefix to the totals.

Is it possible to input the exchange rate for each of the curriences in set cells and then somehow strip out the Eure prfix etc and detect what the currency is and use the relevant exchange rate sell to calculate the GBP price and insert it in the appropriate sell in that row's next column - see attached.

[Richard Buttrey]

Hi,

Does the attached give you a starter for 10?

[irishshopper]

That works Richard but does it mean I have to create two new columns and manually input for each invoice the code for the currency GBP etc and in next column the invoice amount in numerals only ?

Thank you for taking the time to respond.

Trevor.

[Glenn Kennedy]

Here's an alternative, containing only the lookup table.