Hi

You must have a date column in your transactions table. And in convert rate
lookup table, for every rate you must hase the start and end time. This
setup is very similar to one, I created to calculate production time based
on normatives. So I think you can easily adopt it.

I have a sheet Normatives with table:
Article, Normative, ValidFrom, ValidTo

Article column is formatted as text, Normative is a number,
ValidFrom/ValidTo are in date format. Into ValidFrom column the date,
started from which the normative for article was/is valid.
ValidTo is calculated by formula (as example for cell D2):
=IF(OR(A2="",C2="",C2TODAY()),"",IF(ISERROR(MATCH (A2,OFFSET(A2,1,,COUNTA(NormArt),),0)),TODAY(),IF( OFFSET(C2,MATCH(A2,OFFSET(A2,1,,COUNTA(NormArt),), 0),)0,OFFSET(C2,MATCH(A2,OFFSET(A2,1,,COUNTA(Norm Art),),0),)-1,"")))

NormArt is dynamic named range, defined as:
=INDEX(NormativesTbl,,1)

NormativesTbl is dynamic named range defined as:
=OFFSET(Normatives!$A$2,,,COUNTIF(Normatives!$A:$A ,"<")-1,4)

Entries must be ordered by ValidFrom. When there is an entrie for same
article with newer ValidFrom, ValidTo is calculates as previous day from
this new Validfrom. When there is no newer normative, TODAY() is returned as
ValidTo value

Other dynamic ranges are defined too:
NormTime=INDEX(NormativesTbl,,2)
NormFrom=INDEX(NormativesTbl,,3)
NormTo=INDEX(NormativesTbl,,4)

On Production sheet, column B is Date, column D is Machine, column H is
Article. The normative time for p.e. row 2 is calculated as:
=IF(OR(B2="",D2="",H2=""),"",SUMPRODUCT(--(NormArt=H2),--(NormFrom<=B2),--(NormTo=B2),NormTime))

--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )


"Michael" wrote in message
news:agCle.364$xu3.202@lakeread02...
Hi Folks - Here's my situation. I have to convert local currencies into
dollars based on a conversion rate. The conversion rate changes a few
times
per year. So, my sheet looks like this:

Value in Local Currency Local Currency Value in Dollars

100,000 HK 75,000
200,000 DHS 175,000


For instance, say the rate for HK conversions is .55 effective 1/1/2005.
So,
I have many HK conversion transactions based on that rate. (I use a lookup
to determine what currency to convert and what rate to use). Now, on
7/1/2005, the rate changes to .65. If I change the rate in my lookup
table,
then all transactions from 1/1/2005 will change. I need way to preserve
the
rate conversions from 1/1/2005 through 6/30/2005. Any ideas?

Thanks.

Michael