How to autofill X amount of columns based on whatever numeric value X is

Hi,

I've been looking for a solution to the following problem since yesterday afternoon and it has me completely stumped.

I'm looking for a formula or function that will allow me to auto fill "X" amount of columns, based on whatever value "X" is.

For instance;

In the context I'm using it, it is for apprenticeship funding so:

Row 1, Columns 1 through 34 are months i.e. March 2018 through December 2020.

If Billy's apprenticeship lasts 12 months, starting May 2018, then cells May 2018 through May 2019 (12 months) auto fill with the correct formula.

Is there any way to do this?

Any help would be hugely appreciated

Regards

James

Hopefully should be attached now.

Regards

Brilliant, that seems to do the trick.

See below reasoning for 500;

500 is a grant awarded to certain apprenticeships.

I would require 500 adding to the 4th and then last month, based on whether or not a grant is awarded (this is determined in cell j3)

If the grant is not awarded, then the calculation remains 80% of the total cost per month, until the final month where 20%x the amount of months is calculated (in the example for Simon, the calculation returns 100 each month until the final when it increases to 400)

Regards

It appears to be adding the first 500 to the 5th month, rather than 4th month... see below;

month.jpg

Brilliant.

Thank you so much for your help!

Hi,

Apologies for re-opening an old thread, however;

Within the same sheet as discussed earlier, I am now required to utilise an end date function.

Attached is an example of the sheet and the existing formula.

I'm looking to add into the formula that if a date is entered into column I (end date) whatever that date may be, the calculations stop on the named month.

As always, all help is much appreciated.

Regards

Apologies,

Please see re-attached.

Those filled grey are the examples of what I'd require with an end date entered.

Regards

Originally Posted by Bo_Ry

Please try at O4
=IF($G4="","",IF(($H4<=O$1)*(MIN(EDATE($H4,$J4-1),$I4)>=O$1),$G4*0.8/$J4+(EDATE($H4,3)=O$1)*$L4+(EDATE($H4,$J4-1)=O$1)*($G4*0.2+$L4),""))

Hi Bo_Ry,

The above works but does not remove the final payment at the end of the duration of training.

As seen in cells AD4 / AD5