Forum Discussion
Formula Help
Your formula is close but not exactly correct. You need to use a different approach to find the run out date based on stock and orders.
One possible solution is to use a helper column that calculates the cumulative net orders for each part number (Column E minus Column H), and then use a lookup function to find the first date (Column I) that matches or exceeds the stock level (Sheet 1 Column B) for that part number (Sheet 1 Column A).
For example, you can add a new column J in Sheet 2 with this formula in J2 and copy it down:
=SUMIFS(E$2:E2,D$2:D2,D2)-SUMIFS(H$2:H2,D$2:D2,D2)
This will give you the cumulative net orders for each part number.
Then, in Sheet 1, you can use this formula in C1 and copy it down:
=IFERROR(INDEX(Sheet2!I:I,MATCH(A1&B1,Sheet2!D:D&Sheet2!J:J,1)),“”)
This will return the first date that matches or exceeds the stock level for each part number, or a blank if there is no such date.
I hope this helps
...is just a suggested solution, if it doesn't fit your plans, please just ignore it :).
- NikolinoDE I added the first formula into sheet 2,
I then added the formula =IFERROR(INDEX(Sheet2!I:I,MATCH(A1&B1,Sheet2!D:D&Sheet2!J:J,1)),“”)
to sheet 1.
It keeps returning 1/0/1900
Does the lookup function generate automatically?
I tried this but keep getting an error.
=lookup("=IFERROR(INDEX(Sheet2!I:I,MATCH(A1&B1,Sheet2!D:D&Sheet2!J:J,1)),“”)")
How does it read the request dates from Sheet two?
I appreciate your help anything further you can assist with is greatly appreciated!Did you format the cells as numbers?
This can maybe be, because Excel treats blank cells as zero values when performing calculations, and 1/0/1900 is how Excel displays zero dates.
One possible solution is to use an IF function to check if the cell is blank before applying the lookup function.
For example, you can try something like this:
=IF(ISBLANK(Sheet2!A1),“”,IFERROR(INDEX(Sheet2!I:I,MATCH(A1&B1,Sheet2!D:D&Sheet2!J:J,1)),“”))
This formula will return an empty string if Sheet2!A1 is blank, otherwise it will perform the lookup function as usual.
NikolinoDE I have attached a sample Excel sheet very simple with just a couple of items
I still can not get the formula to work, not sure if I am missing something.I really do appreciate the help!!
