Mar 20 2023 02:13 PM
Excel Sheet 2:
Column D are the different part numbers we offer.
Column E is the quantity ordered on a specific Purchase order.
Column H is the quantity shipped from that specific purchase order.
Column I is the requested date from that specific purchase order.
Excel Sheet 1:
Column A is the part number
Column B is the stock levels of the different part numbers
I want to look for a specific part number (Column D) and sum all the quantities on purchase order (Column E), while subtracting what has already been shipped (Column H).
Then taking the stock levels from Excel sheet 1 ( Column B) of a specific part number (Column A) to figure out how long stock will last per purchase orders (Column I), and then return a specific date (03/06/2023).
We sell Apples, Oranges, and Bananas.
Multiple purchase orders with different due dates.
We are searching how long the Apple stock will last based on Open Sales Orders / partially invoiced Open Sales Orders.
We have 100 Apples on sales orders, with 10 already being shipped leaving 90 Apples on Sales Orders.
We have 80 Apples currently in stock.
I want to find out that on 03/06/2023 we will be short on Apples and not able to cover the sales order.
I want it to return a runout date not specifically 03/06/2023 that was just an example
This formula is close, but I need it to sum through open sales orders and then return the run out of date once the stock is not sufficient enough to cover that particular part number based on Open Orders. Find that column E doesn't have enough stock after deducting Column B from Sheet 1 to cover the open sales orders, then on the same row return Column I for the requested date shortage.
=IF(SUMIFS(Sheet2!E:E,Sheet2!D:D,A1)-SUMIFS(Sheet2!H:H,Sheet2!D:D,A1)>B1,"03/06/2023","")
Mar 21 2023 12:44 AM
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 :).
Mar 21 2023 06:41 AM
Mar 21 2023 06:50 AM
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.
Mar 21 2023 07:16 AM
@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!!
Mar 21 2023 08:09 AM
Mar 21 2023 09:10 AM
Sub stock()
Dim i, j As Long
Dim sum As Long
For j = 1 To 3
For i = 2 To 100
sum = Application.WorksheetFunction.SumIf(Range(Cells(2, 4), Cells(i, 4)), Sheets("Sheet1").Cells(j, 1).Value, Range(Cells(2, 11), Cells(i, 11)))
If sum > Sheets("Sheet1").Cells(j, 2).Value Then
Sheets("Sheet1").Cells(j, 3).Value = Cells(i, 9).Value
Exit For
Else
End If
Next i
Next j
End Sub
You can try these lines of code. In the attached file you can click the button in cell B4 of Sheet2 to run the macro.
Which version of Excel do you work with? Do you have Office 365?
Mar 21 2023 11:24 AM
Mar 21 2023 11:25 AM
Mar 23 2023 12:33 PM
The results are returned in range C1:C3 of Sheet1. The code refers to Sheet1 and Sheet2.
Sheet1:
Sheet2:
You can copy the code from my last reply and paste it into a module of your VBA editor. Then you can run the macro in your file as well.
Mar 29 2023 06:55 AM
I keep getting a
Compile Error:
Expected end sub
I was able to create some simple Macros but was not able to use this code
I created a commandbutton
Private Sub CommandButton4_Click()
Sub stock()
Dim i, j As Long
Dim sum As Long
For j = 1 To 3
For i = 2 To 100
sum = Application.WorksheetFunction.SumIf(Range(Cells(2, 4), Cells(i, 4)), Sheets("Sheet1").Cells(j, 1).Value, Range(Cells(2, 11), Cells(i, 11)))
If sum > Sheets("Sheet1").Cells(j, 2).Value Then
Sheets("Sheet1").Cells(j, 3).Value = Cells(i, 9).Value
Exit For
Else
End If
Next i
Next j
End Sub
Mar 29 2023 07:05 AM
Mar 29 2023 08:48 AM
The code returns the expected result in my file. Can you attach your file without sensitive data?
Mar 29 2023 08:57 AM
Not sure if this matter also, but the range will go down to about 5,000 line items
Thank you for all your help!!
Mar 29 2023 10:08 AM
This formula seems to "semi-work"
It is not returning all the products even though they are on both sheets
Once again I appreciate all your help.
Maybe other tweaks need to be made?
Private Sub CommandButton1_Click()
End Sub
Sub stock()
Dim i, j As Long
Dim sum As Long
For j = 2 To 3000
For i = 2 To 1000
sum = Application.WorksheetFunction.SumIf(Range(Cells(2, 4), Cells(i, 4)), Sheets("Sheet1").Cells(j, 1).Value, Range(Cells(2, 11), Cells(i, 11)))
If sum > Sheets("Sheet1").Cells(j, 2).Value Then
Sheets("Sheet1").Cells(j, 3).Value = Cells(i, 9).Value
Exit For
Else
End If
Next i
Next j
End Sub
Mar 29 2023 11:40 AM
You can click the button in cell N3 of your file to run the macro. The code dynamically determines the number of inventory in column A of sheet1 and the number of entries of items in column D of sheet2. I've entered additional inventory and the code returns the expected results. For inventory "banana" no date is returned because the "on hand" quantity is always bigger than the quantity in sheet2. 5000 line items in sheet2 could mean that the code runs for a few seconds.
Apr 03 2023 03:12 PM
I am able to get the formula you stated prior to work, but only if it is in a worksheet with just those two files and if I change the code for J and For I to 1 to 3000 and 2 to 1000.
Is there a way to integrate this into my workbook that has multiple sheets, but only to still calculates the two sheets together?
Also, can we choose where the displayed field is returned? it always comes into column C, but when other columns are filled in, it does not return any dates.
I would like to integrate this into a worksheet that also has other information on it but "A" remains the item "B" remains stock level and then "C" can be the returned run-out date but then I have "D" on filled with other information. "This is on sheet 2 and sheet 1 remains unchanged.
Does the name of the worksheets matter as well, I noticed there is a "true worksheet number from when the worksheet is created, and then you can alter the tab, does this make a difference?
I sincerely appreciate all of your help!!!