I have attached an example spreadsheet of what I am trying to accomplish here. Basically all of the rows with a date prior to today will be actual values pulled in from an external database.
In cells E2:M3 I have the different stages of our supply chain and how many days each stage takes. As units move through the supply chain they should stay at each stage for the number of days indicated in the table in E2:M3, with two exceptions:
Stage 1 is the ordering stage. You can see my formula starting at cell F29. This formula checks if the day is divisible by the reorder frequency, and if so, checks to see if we go below the safety stock number and need to order more inventory. If the total stock falls below the safety stock number in the period being checked, and order will be placed for the reorder amount.
Stage 8 is the other exception. This is our total stock available for sale. This number should check if any stock is available from completing stage 7, as well as subtract the unit sales from the previous day (Column O).
The issue I am having is writing a formula that will repeat the unit number each day until the number of days in the supply chain length table has been reached, and then moving those units to the next stage. Any formula I write results in a circular reference in the formula for stage 1.
Happy to provide any other details as needed.