[B]B4: =N(A4)+B3 [/B]
Calculates running total of queue items.
The N() function avoids an error if non-numeric value is in referenced cell.
[B]A9:=A2 [/B]
Carries down start date to ensure production table starts on same date as the inventory in Rows 2:4
[B]A10: =A9+IF(WEEKDAY(A9)=6,3,1) [/B]
Lists dates and skips weekends. If previous date was a Friday, returns
next Monday's date, otherwise returns the next date.
[B]C9: =B9+N(C8) [/B]
Calculates running total of forecasted production. The N() function avoids an error
if a non-numeric value is in referenced cell.
[B]D9: =IFERROR(MATCH(C9,$B$4:$BN$4,1),0) [/B]
Finds the largest value in the Queue Running Total (Row 4) that is less than or equal
to the lookup value (the Running Production Total for that date). Returns the
Column number for that matched item. The IFERROR part handles the scenario
that no values in the Queue Running Total are less than the lookup value.
[B]E9: =INDEX($B$2:$BN$2,1,D9+1) [/B]
Returns Queue Date by looking in the Inventory Dates (Row 2). The Queue Date
will be one Column to the right of the Column found by the Match formula in D9.
[B]F9: =A9-E9 [/B]
Calculates Age of Queue by subtracting Queue Date (10/21/2015)
from Production Date (1/11/2016)
[B]G9 =INDEX($B$4:$BN$4,1,D9+1)-C9 [/B]
Calculates how many items will be remaining from the Queue Date's inventory.
Subtracts the Running Production Total from the Queue Running Total for
the Queue Date and returns the difference.