"TomCat" wrote:
Max and Biff, thanks for your help. Biff, I could never get yours to work.
Max, yours worked great except in a couple of lines where it's off by .53.
That will be my mission for the morning.

Maybe re-check the values within the lines.
If it works, it should work ok for all lines as we copy down from CT53

Max: I need an explanation of what you did. Can you walk me thru the
formula? I saw some double commas there? I'm not familiar with that syntax.
If you could be so kind as to step me thru it!

At the crux of it is the OFFSET expression within the SUM(OFFSET(...))
ie, in partially decomposed form, the expression:

OFFSET(K53,,
MATCH($CU$46,$L$47:$CQ$47,0),,
MATCH($CV$46,$L$47:$CQ$47,0)-MATCH($CU$46,$L$47:$CQ$47,0)+1)

From help, the syntax is: OFFSET(reference,rows,cols,height,width), so for
the above

reference point is K53
ie the row 53 in question

rows param is zero (the optional zero is omitted here, with the rows param
represented by the double commas next to K53, viz.: K53,,)

cols param is returned by the expression:
MATCH($CU$46,$L$47:$CQ$47,0)
viz the position of the matched EOM startdate (CU46)
within the EOM array $L$47:$CQ$47

height param is zero (the optional zero is omitted here, with the height
param represented by the double commas next to the cols param, ie:
MATCH($CU$46,$L$47:$CQ$47,0),,

width param is returned by the expression:
MATCH($CV$46,$L$47:$CQ$47,0)-MATCH($CU$46,$L$47:$CQ$47,0)+1
which is essentially the difference between the matched EOM enddate's (CV46)
position within $L$47:$CQ$47 minus the matched startdate's (CU46) position
within $L$47:$CQ$47 plus "1" (+1) as an arithmetic adjustment

So the expression OFFSET(K53,...) will evaluate and return the range/array
of values within L53:CQ53 starting with the col of the matched EOM startdate
and ending exactly where the matched EOM enddate col lies

And when the OFFSET(K53,...) is copied down, the reference increments
relatively to OFFSET(K54,...), OFFSET(K55,...), and so on, hence returning
correspondingly the required range of values in row 54, 55, and so on as we
copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---