Formula question: How to take sales from one month and show the revenue X months later (where X is a variable we can change)

How to create a formula that shows sales in one month and we can choose to show revenue in the next month or a two month delay or a three month delay etc.

The reason for this is, say we make a sale in January, but we want the projection sheet to show no revenue from that sale for 4 months (or 2 months or 3 months or 5 months), then we can use the first part of the formula (changing F16 to a 1 or 2 or 3 or 4) to trigger when the output starts, meaning January sales show revenue in May, February sales show revenue in June, March sales show revenue starting in July etc.

But then we may want to view the projection where the revenue is only delayed by one month, so we change the variable in F16, but the rest of the formula does not work. We can get the delayed output to show, but not pull the number from the proper cell and then also have each subsequent cell to the right update in order.

=IF(($I$4+$F$16)<=I4,$I$9,"0")

=IF(($I$4+$F$16)<=I4,$I9,"0")

=IF(($I$4+$F$16)<=I4,I$9,"0")

=IF(($I$4+$F$16)<=I4,I9,"0")

In the above formula, no variation works for what I need to achieve.

=IF(($I$4+$F$16)<=I4 (this part of the formula does what is intended.

Depending on what number is typed in cell F16, “0”s appear in all cells until (($I$4+$F$16)<=I4 trigger the out of I9.

The problem is, when a number other than “0” finally starts appearing, I need it to start with whatever number is in I9, then in the next cell J9, then K9, then L9.

$I$9 works for the first cell, but all cells to the right of course only output the number form I9.

None of the others work, because when a number finally appears, the output is not starting with I9.

I need the number when =IF(($I$4+$F$16)<=I4 triggers the first output, to start with I9 then allow all cells to the right to pull output from cell J9, then K9, then L9. Etc.

SAID ANOTHER WAY
I have the field F16 set up so I can enter any number.I4 = 1 (as in Month 1)

J4=2 (as in Month 2)

K4=3 (as in Month 3) etc

In most cases I will want to view the projections where F16 is set from 0 to 5 (Meaning if a sale comes in Month 1 where I4 = 1 (Month 1), I can set F16 with a number that delays how many months before the revenue associated with Month 1 shows up.

• If I enter 0 IN F16, there is an output in cell I10 because =IF(($I$4+$F$16)<=I4 triggers this
• F16=0 & I4=1, so 0+1=1 which is less than or equal to I4 (Month 1 or 1).
• If I enter 1 IN F16, there is an output starting in cell J10.
• If I enter 2 IN F16, there is an output in cell K10.
• If I enter 3 IN F16, there is an output in cell L10.
• If I enter 4 IN F16, there is an output in cell M10.
• ETC.

The problem is, I always need the first cell where an output appears to come from “I9”.

Then, every cell to the right of that must be pulling from the next cell one by one.

• If I enter 0 IN F16, there is an output in cell I10 and it needs to pull from “I9”. But J10 needs to pull from J9. And K10 needs to pull from K9.
• If I enter 1 IN F16, there is an output starting in cell J10 (and it needs to pull from “I9”.) But then in K10 I need data from J9, and in L10 I need data from K9 etc.
• If I enter 2 IN F16, there is an output in cell K10 (and it needs to pull from “I9”.), BUT then I need data in L10 to come from J9, and data in M10 to come from K9 etc.
• If I enter 3 IN F16, there is an output in cell L10 (and it needs to pull from “I9”.). BUT then I need data in M10 to come from J9, and data in N10 to come from K9 etc.
• If I enter 4 IN F16, there is an output in cell M10 (and it needs to pull from “I9”.). ETC. BUT then I need data in N10 to come from J9, and data in O10 to come from K9 data in P10 to come from L9 ETC.

So, wherever the data first shows up (cell I10, J10, K10, L10, M10, N10… the output always has to come from I9, then the next cell to the right must come from J9, then K9 etc.

I use I4 as the first month a sale is made, but the revenue may show up in the same month or 1 month later, or 2 months or 3 months later etc, and I use F16 to change when the revenue start to show in one of the cells of row 10 (based on the delay I select in F16.

So in this case the suggestion does not work, but I think I had not fully explained the issue I am having. I hope the above helps.

The  issue is that until I enter a number in F16, I do not know which cell in row 10 will have the first output but whenever that is (based on the number entered in F16, it needs to start with output from I9, then the next cell over to the right J9, etc