Automatically complete availability dates in a MRP planning sheet

[alin.]

Hello, Excel masters

I've just joined this forum hoping I can find the answer to a small problem I'm facing and I humbly ask for your ideas to solve it.
I have tried to find the same problem in previous posts, but there are so many of them and time is not on my side.

The attached example shows a basic material requirements/planning sheet, in which:
- column A: Date when the quantity is planned to enter or exit the warehouse
- column B: The quantity planned to enter/exit the warehouse
- column C: The balance quantity

I wonder if there is a way (standard Excel formulas or VBA macro) to show in column D the date when the negatives will be fulfilled (either in the past or in the future). I have calculated manually the dates in column E, but I cannot replicate the logic with formulas.

I use Office 365 on regular basis but on the lower level of difficulty.
Please let me know if there is any other info I need to provide.

Thank you,
Alin

[dflak]

I added a helper column or two.

The first calculates the row on which the balance returns to positive. =IF(C2<0,MATCH(TRUE,C3:C14>0,0),"") - This used to be entered in as an array formula. Now it works as a regular formula. What it basically says is if the balance is negative in this row, look at the rows below and return the relative row where the balance first goes above zero. By relative row, I mean that if 1 is returned that means row 3 (the first row below the negative balance.) If 2 is returned then it means row 4 (the second row below the negative balance) and so on.

The next formula is =IF(ISNUMBER(E2),INDEX(A3:A14,E2,1),"") - This means look at the range of dates below the negative value, look on the row computed in column E and return the value from that.

There is a "gap" in the range to look at. I am always looking that the 11 rows below. When you get to the bottom of the table, you are still looking at 11 rows below. In theory, I should be searching for the end of the data, but I am assuming that the balance will go positive within 11 rows. I am assuming that you will be adding rows and copying down the formula, so I left it "floating."

[alin.]

Thank you very much, dflak!

You made it look so easy. I wish I will have the same wisdom someday...

I now realize my initial request is wrongly formulated: I need the negatives from the column B to have the date of availability filled in. Thatīs why I have put manually the values on the negative rows of column B, just to guide the logic.

I will try to use your solution as the starting point and move forward, but may I dare ask you to try and take a look also?

Thanks again,
Alin

[JeteMc]

Hello alin and Welcome to Excel Forum.
I cheated a bit to come up with this proposal.
In instances where column B has a negative and a positive value for the same date, the negative value is listed first.
The formula in column D is then: =IF(C2<0,AGGREGATE(15,6,A3:A$16/(B3:B$16>-C2),1),IF(B2<0,AGGREGATE(14,6,A$1:A1/(C$1:C1>-B2),1),""))
Let us know if you have any questions.