Forum Discussion
Excel
Looking at open sales orders:
If I have a column that is labeled inventory part number "A" (Example: But can range from row 1 to 30 or 1-5000)
If I have a column that is labeled quanitity per purchase order "B" (Example: But can range from row 1 to 30 or 1-5000)
If I have a column that is labeled requested date "C" (Example: But can range from row 1 to 30 or 1-5000)
Then I have a cell of inventory levels "D" and "E" (One cell for each level)
(An assembly and Subassembly)
Can I have Excel look at the Part number "A", Talley up the QTY "B", and then subtract "-" stock inventory levels "D+E", while looking at the requested date and have Excel tell me by 8/16/2022 You will be "X-amount" short for that order?
I'm going to say "Yes" to your question. Yes, Excel could do that.
But I suspect that answer would not be altogether satisfying.
The truth is there probably are several ways to do what you're asking. Rather than take a stab in the relative darkness, it would help if, in addition to your verbal description, you could post an actual copy of your spreadsheet (or a mockup of it), so that I or somebody could work with the real thing. If you can't post it here, use OneDrive or GoogleDrive and post a link here.
- mathetes the file won't upload for some reason, I have copied and pasted the short mockup
Can I have Excel look at the Part number "A", Talley up the QTY "B", and then subtract "-" stock inventory levels "D+E", while looking at the requested date and have Excel tell me by 8/16/2022 You will be "X-amount" short for that order?
This list contains multiple part numbers
The list usually will not range past 5000 Rows
This is the formula I was able to come up with to at least Talley the QTY and subtract out 00+73
=SUM(D3+E3)-SUMIF('Open Sales 010120.xlsx'!$A$2:$A$1045,"10011",'Open Sales 010120.xlsx'!$B$2:$B$1045)
Assembly (00) Sub-Assembly(73)
Part Number Qty Request Dates 00 73
10011 20 7/25/2022 15 90
10011 20 7/26/2022
10011 21 7/29/2022
10011 25 8/1/2022
10011 20 8/2/2022
10011 45 8/4/2022
10011 20 8/8/2022
10011 10 8/15/2022
10011 40 8/16/2022
10011 10 8/29/2022
10019 128 7/27/2022 100 30
10019 1 8/16/2022
10019 2 8/17/2022
10019 128 8/25/2022
10019 20 7/26/2022
10019 30 8/2/2022
10020 15 8/8/2022 75 0
10020 100 8/11/2022
10020 20 8/15/2022
10020 5 8/23/2022
10020 10 8/29/2022
10020 100 9/8/2022
10020-1 2 8/17/2022 0 1
10020-2 128 7/27/2022 133 0
10020-2 2 7/27/2022
10020-2 1 8/1/2022
10020-2 2 8/3/2022
10020-2 1 8/10/2022
10020-2 25 8/12/2022
10020-3 50 8/22/2022 9 70
10020-3 50 9/23/2022
10020-3 50 10/21/2022
I understand if you do not want to look at this, I do apologize the file keeps failing the upload. Thank you for even considering helping!the file won't upload for some reason, I have copied and pasted the short mockup
Please use OneDrive or GoogleDrive and post a link to the file. That you can do, I'm assuming.
