Please can someone help with a Forex Type Spreadsheet

Perhaps this will help.
I combined the two tables on the Non-JPY sheet with the "Multipliers" for JPY on row 1.
The formula used in F4:F7 is:

Formula:

=IF(D4="","",IF($D4="Buy",$E4+IF(ISNUMBER(SEARCH($C$1,$C4)),F$1,F$2),$E4-IF(ISNUMBER(SEARCH($C$1,$C4)),F$1,F$2)))

Let us know if you have any questions.