Quote Originally Posted by kistev
hello

I am new at this and have searched the forum but unfortunately not found anything that will help

I have a spreadsheet where all our stock purchases are recorded. The purchases are in Euro and have to be converted to Sterling at the rate valid for the month when the payment was authorised.

See the attached spreadsheet :testform.xls - zipped

I want to be able to do the calculation automatically:

for example

Order x1234 was approved for payment in July.

That is check the date in column N falls in July and if it does multiply the value in column H by the relevant exchange rate in column X (July = 1,42 and place the resulting value in column I.

Using IF statements 7 is the limit, so I am stuck as I assume there are 2 ranges that need to be checked.

Any help would be greatly appreciated

Thanks
Hi,

the easiest way for this is a table of Date - Rates

whilst the table could be anywhere, a separate sheet is recommended, Rates, in column A the date the rate starts, in column B the rate from that date.

For the cell where you want the rate to appear, and assuming the 'date' is in column N

=VLookup(N2,Rates!A:B,2,True)

will set the rate.

Make sure the sheet Rates!A dates are in correct date sequence.

hth
---