Sum a number of rows back based on value

Hi everyone.

i am building an MRP (materials requirement planning) system in excel and am some kind of stuck on the following.

What i would like excel to do is, based on the lead time (in weeks) that there is a formula to make a cell look at other cell and sum. is this possible?

I Uploaded an example here http://www.filedropper.com/mrp .

thanks.

I don't follow where you got the value in D10. Based on C10, though, if your goal is to get the sum of the planned receipts from the given column to the column X periods away, based on lead time, see if the formula below does what you want. Paste it in C9 and fill right.

=SUM(C$8:INDIRECT(CELL("address",OFFSET(C$8,0,$D$2))))

Hi cantoh,
The idea behind the line 10 is, based on the lead time (in week), The planned receipt (line 9) must be placed and summed in the line 10.

Thanks

I'm still not quite following how the value 58000 fits in D10. What would go in E10 and F10?

Hi cantosh,
Line 9 planned order receipt = quantity, when orders need to be received (calculated based on Lot quantity)
Line 10 planned order release = when these quantity need to be placed to receive on time (based on lead time).

Example : in order to receive qty 9000 in week 4 and 7000 in week 5 an order of 16000 should be placed in week 1.

Thanks,

cantosh,
I Uploaded an example.
Hope the example can help me understand what i mean.
Thanks.

Thank you, I think that did help my understanding. The only thing that throws me off now (I think) is the value of 58,000 in cell D10 - your Planned Orders (what should be) for period 2. By my understanding, shouldn't this value be equal to the planned receipts in period 6 (80,000)? Why is it equal to the NET POQ Req for Period 6?

Oh no, it's a mistake, actually it should be 80,000 !

Okay, good. I think I get it then. Try this in C9 (or C10) and fill right:

=IF(RIGHT(TRIM(C$3),1)="1",SUM(C$8:INDIRECT(CELL("address",OFFSET(C$8,0,$D$2)))),OFFSET(C$8,0,$D$2))