DOR YOU ARE ABSOLUTELY HUGE!

I have been searching and trying differnt things for months... you guys are
always my last resort and you never cease to come through.

Thank you DOR for actually reading the "word problem", understanding and
solving not only what I was looking for but you also had the foresight to
include the ability to archive the current data and move it into the next
level.
Outstanding time frame!!!

I can't say enough good about you guys. You ARE the Best.
PS I will try that two row formula for speed because I have a very large
worksheet so I am sure it will help.
Luke

"DOR" wrote:

Try in C10

=IF(MOD(C6+IF(YEAR(B1)=YEAR(C1),B5,0),1000)<MAX($ A$7:$A$9),MOD(C6+IF(YEAR(B1)=YEAR(C1),B5,0),1000) ,MOD(C6+IF(YEAR(B1)=YEAR(C1),B5,0),100))

This assumes that when you add new columns, the comparison is always
against the column immediately to the left except for A7:A9 which is an
"absolute" range and all comparisons are made against that, that is,
column C compares against column B, column B against column C, etc.,
except that they always compare against the maximum of A7:A9.

The formula could be shortened considerably if you added another row to
contain

=MOD(C6+IF(YEAR(B1)=YEAR(C1),B5,0),1000) which occurs twice in the
above formula.

If you entered that formula in C11, then the C10 formula would be

=IF(C11<MAX($A$8:$A$9),C11,MOD(C11,100))

I think these formulas reflect the logic you outlined. Let us know if
they do or don't.

HTH

Declan O'R