Foreign exchange matrix

Forex is doing my head in. I'd be grateful for some pointers, or otherwise to share the grief.

I have various amounts in four different currencies. Each amount needs to be translated to each other currency.

So I have a matrix of source data, a matrix of currency pairs (that's 25 combinations) and I need a matrix of converted currencies.

I'll upload a workbook...!

In the purple cells 'piv'!G:J, you can see the sheer pain of manual formulae.

Question: what formula in the purple cells reads the:

• data;
• the source currency (green, row 4); and
• the destination currency (purple, row 3)?

The forex table is in sheet 'fx'. GBPUSD=1.5, therefore its inverse USDGBP=0.6667. To multiply the data by the conversion rate, the source currency is B1:F1 and the destination currency is A2:A6. The workbook has two named ranges in it, derived from a previous failed experiment.

Hi
Use in FX!H1 the following formula and copy forward to K1

Formula:

Please Login or Register  to view this content.

Use in FX!H2 and copy forward and down to K6 the following formula

Formula:

Please Login or Register  to view this content.

Use in piv!G5 the following formula and copy down and forward

Formula:

Please Login or Register  to view this content.

See the file

Try

In G5

=SUMPRODUCT(($B5:$E5)*(FX!$A$2:$A$6=piv!G$3)*(FX!$C$2:$F$6))

Copy across and down

NOTE: the FX table headings need to be in the same sequence as your data headings:

GBP EUR USD NOK