I have been wrestling with this setup for a while and I may have overthunk (sic) it to the point of missing the obvious.
I use a spreadsheet to keep track of all our projects’ percent complete and subsequent recognized revenue, thus for the first project: Sheet1! A2 = Gross Contract; Sheet1! B2 = Current Pct Complete; Sheet1! C2 = Sheet1! A2* Sheet1! B2 ( Recognized Revenue)
Sheet1! B2 is updated monthly from a separate worksheet schedule input – a basic table for data input: Sheet2! A2:L2 = Jan thru Dec; Sheet2! A3:L3 = Respective Percent Complete.
I use an IF function to have Sheet1! B2 read backwards from Sheet2! L3 until it finds a pct entry >0. This is the Current Pct Complete.
Another calculation on Sheet1! Involves tracking Revenue monthly based on the change in Pct Complete and it looks something like this:
Sheet1!D2:O2=Rev Jan thru Rev Dec;
Sheet1! D2 = (Sheet2! A3* Sheet1! A2) – (either 0 or the revenue recognized as of 12/31 of the previous year from another schedule);
Sheet1! E2 = IF Sheet2! B3>0,( Sheet2! B3* Sheet1!A2) – Sheet1!D2, 0;
Sheet1! F2 = IF Sheet2! C3 >0, (Sheet2! C3 * Sheet1! A2) – (Sheet1! D2 + Sheet1! E2),0;
Sheet1! G2 = IF Sheet2! D3 >0, (Sheet2! D3 * Sheet1! A2) – (Sheet1! D2 + Sheet1! E2 + Sheet1! F2),0
and so on across to Sheet1! L3.

My problem is that I need something that will not affect any calculations made if I need to adjust the amount of the gross contract (Sheet1! A2).
In other words up to that point any revenue recognized can not be retro changed.
Additional revenue after the change will be based on the amount of the adjusted contract, up to 100% of the new amount.
I’m too new at VBA to get to an effective event but I ‘m thinking that a change event might be the answer.
Any suggestions are greatly appreciated..thanks for reading.