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