Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Formula Help

Copper Contributor

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","")

16 Replies

@LearningFurther 

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!

@LearningFurther 

 

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!!

I didn't look at it (unfortunately I didn't have enough time - I'll be able to look at it tomorrow when I have time), but change the quotation marks, sometimes they're not comfy when copying.

@LearningFurther 

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?

@NikolinoDE I changed the " and I am still returning the same error.
The date columns are Date
Everything else is set to general.

I really do appreciate the help, seems we are very close to figuring this out!
@OliverScheurich

Would this look over Sheet 1 and Sheet 2?
Also, I am unable to use this is states it's from an "untrusted" source and will not allow me to open it.
I have never worked with Macros before, but am willing to learn.

@LearningFurther 

The results are returned in range C1:C3 of Sheet1. The code refers to Sheet1 and Sheet2.

 

Sheet1:

sheet1.JPG

 

Sheet2:

sheet2.JPG

 

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.

@OliverScheurich 

 

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

Along with a compile error: Ambiguous name detected: Stock

For product part numbers I usually use a heard "Item" and
"QTY" on sheet 2

On sheet 1

The part number would be "Inventory" and the stock level would be "On Hand"
Not sure if this changes anything in the code?

@LearningFurther 

The code returns the expected result in my file. Can you attach your file without sensitive data?

 

@OliverScheurich 

 

Not sure if this matter also, but the range will go down to about 5,000 line items

 

Thank you for all your help!!

@OliverScheurich 

 

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

@LearningFurther 

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.

@OliverScheurich 

 

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!!!