Spread cell value over multiple cell based on dates

Trying to spread projected sales revenue over a number of months based on projected order date.

Inputs - projected order month, total revenue, number of months the job is expected to last.

Have tried -

Created a 12 month calendar - compare expected order month with calendar month - if yes, then spread order based on number of months

=IF($H$7=$L$6,IF(COLUMNS($L7:L7)>$F7,"",$G7/$F7),"")

But it only works for the first date occurrence. (i.e. this formula test for month of October, next cell tests for November. When expected order month is October will not spread data in cell with November test.

Sample attached.

Thanks,

Hi - I believe the formula in L7 is wrong. I copied K7 to L7 and that seems to have fixed it.

Originally Posted by jbrigger

Hi - I believe the formula in L7 is wrong. I copied K7 to L7 and that seems to have fixed it.

I see what you did, but if the order date changes you have to manually change the formula in the calendar table.

I am looking for a solution where the order value is spread automatically.

Thanks for your input.