Excel spreadsheet problem

saad_mce

New Member
Joined
May 9, 2018
Messages
5
Hi all,

I have got a spreadsheet problem that I need the help of the wizards in this forum.
I download this data from our ERP system on finished goods SKUs. There few hundred SKUs.
The data up to column H is from the ERP system. The data starts from today's date to show the on hand stock today. It then goes on to show the stock consumption by customer orders and stock increase by production.
The type of demand is noted in column C and the stock amount consumed by demand or added by production is shown is column G.

I use a sumifs formula in the 1st calculated column (column I) to calculate the available quantity after each addition/subtraction of stock (SUMIFS(G$2:G2,A$2:A2,$A2)).

The solutions required for the below issues. The spreadsheet has 100s of SKUs sorted by SKU code (column A) and date of activity of that SKU (column D).

1. My problem starts with 2nd calculated column (column J). I want to run a formula that will be blank if we have stock today and "OOS now" if we have zero or negative stock today. I can do this formula alone, but could not find out how to combine with the next set of problems.

2. If we go out of stock in future before next production or between two productions, I want the column to say "POOS before Prod" (potential out of stock). This requires looking at the available Qty column (column I) and Rec/reqd quantity (column G)

3. If we have no prod planned production for this SKU, I want the formula to say "POOS & no Prod Planned". Again the formula needs to look in the negatives of column I and if there are any receipts in column G.

Any suggestion will be greatly appreciated.

A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
MaterialMaterial DescriptionMRP elementPlanned datesMRP element dataException MessageRec./reqd quantityRescheduling dateAvailable QtyIssues
10007​
Product 1Stock
26/05/2021​
3,9603,960
10007​
Product 1Demand
28/05/2021​
0010722781/00010- 1,5122,448
10007​
Product 1Demand
27/08/2021​
0010679907/00010- 1,512936
10007​
Product 1Demand
27/10/2021​
0010772594/00010- 1,512- 576POOS before Prod
10007​
Product 1PldOrd
29/10/2021​
0001489363/STCK**
10​
6,048
27/10/2021​
5,472Production
10017​
Product 2Stock
26/05/2021​
--OOS now
10017​
Product 2Demand
28/05/2021​
0010841763/00010- 12,096- 12,096POOS before Prod
10017​
Product 2PrcOrd
1/06/2021​
000000908773/YFIL/Re
10​
12,096
28/05/2021​
-Prod
10017​
Product 2Demand
27/09/2021​
0010840748/00010- 12,096- 12,096POOS before Prod
10017​
Product 2PldOrd
29/10/2021​
0001426462/STCK**
10​
12,096
27/09/2021​
-Prod
10020​
Product 3Stock
26/05/2021​
5,0005,000
10020​
Product 3Demand
28/05/2021​
0010841763/00010- 12,096- 7,096POOS & no Prod Planned
10020​
Product 3Demand
1/06/2021​
0010841763/00011
10​
- 12,096
28/05/2021​
- 19,192POOS & no Prod Planned
10020​
Product 3Demand
27/09/2021​
0010841763/00012- 12,096- 31,288POOS & no Prod Planned
10020​
Product 3Demand
29/10/2021​
0010841763/00013
10​
- 12,096
27/09/2021​
- 43,384POOS & no Prod Planned
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

saad_mce

New Member
Joined
May 9, 2018
Messages
5
Hi,
I tried multiple ifs. Problem is the formula has to look not just at the same row, but to the whole column for two columns. This probably requires combining array with sumifs.
 

Forum statistics

Threads
1,148,191
Messages
5,745,251
Members
423,939
Latest member
Learnexcel1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top