Auto populating cells with values - depending on dates / other criteria

[jamesproudlock]

Hi everyone

I've been having some trouble with the attached sheet.

I created this for forecasting finances based around apprenticeships. The premise being (as is clearly shown in the sheet) that the sheet will forecast income per month based on a total cost of a course across the duration of said course (this works out at the total cost minus 20% divided over the duration - with the final month of the course returning the 20% as well as that months percentage of the total cost).

There is also cope for an additional payment to be made after 90 days and 365 days of the course - this I have under control.

However; I have been asked to make some changes to this to allow for the following rules;

- A learner may take a "break in learning" - this causes problems for the current incarnation of the sheet in that it is rigid to the start / end date / duration entered when inputting a new learner. Despite their status changing from "active" to "break in learning" the distribution of the fees will not change.

Person 7 - has a break in learning dating 11/09/2018 but then becomes active again 2 months later BIL date 11/12/2018 - this would display itself on the dated graph next to the inputs with two blank entries for months October/November 2018 but then commence again afterwards.
Person 8 - has a break in learning dating 11/09/2018 but no BIL end date meaning their entries stop at the resulting month.

I need the formulas in the cells AO > DV to factor in if there is a break in learning the monthly distributions stop at the BIL start date and begin again at the BIL end date - and stop as planned on the NEW PED date

Any help is HUGELY appreciated.

Many thanks