How to overide current cell date & adjust yearly volume if another cell date is present.

[billrogers184]

I currently have yearly amounts (over several years) that are distributed into monthly values and placed in the correct year. This is based on start and stop dates, which affect the total number of weeks in a given year that the amounts have to be produced within. Now there is another possibility of the end date changing, which would change to total yearly amounts (current and future amounts). I want to reduce the yearly amounts based on the "new possible" end date, only if this "new possible" date is listed in an adjacent column. If it is not listed, then the formula would simply continue as is. This could also eliminate future amounts in future years based on the new date.

I can get the formula to override the current date if there is a "new possible" date listed, but can not figure out how to get the total amount to adjust based on this "new possible" date.

See attachment and thanks very much for any support.

[JeteMc]

Cell AO28 can either contain a static value like 54,000 or a formula, but not both. If there is a way to arrive at the original value in AO28 (54,000) using a formula then what you are asking is probably possible. Caveat is that the formula could not include the Production per wk based on EOP value in cell BB28 (unless there is a different method to calculate that value) as that would set up a circular reference.
Knowing how you calculated that the new value should be 27,218 may help as the calculation mentioned in the file AO28/AW28 results in 2232.