handri
Board Regular
- Joined
- Nov 25, 2017
- Messages
- 88
- Office Version
- 365
- 2021
- Platform
- Windows
Good day
I m looking for the shortage item in my sample data to extract into information in details
A USE APPLE 1
B USE APPLE 5
C USE APPLE 6
PO EACH ONE IS 100
APPLE on hand is 500
meant PO*APPLE
TOTAL USE IS 1200
500 minus 1200=-700
How can i extract the shortage -700 of apple into information like my data sample below
thanks
I m looking for the shortage item in my sample data to extract into information in details
A USE APPLE 1
B USE APPLE 5
C USE APPLE 6
PO EACH ONE IS 100
APPLE on hand is 500
meant PO*APPLE
TOTAL USE IS 1200
500 minus 1200=-700
How can i extract the shortage -700 of apple into information like my data sample below
DATA SAMPLE.xlsx | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | BILL OF MATERIAL | ||||||||||||
2 | |||||||||||||
3 | MATERIAL | A | 100 | B | 100 | C | 100 | TOTAL USE | ON HAND | BAL | |||
4 | APPLE | 1 | 100 | 5 | 500 | 6 | 600 | 1200 | 500 | -700 | |||
5 | ORANGE | 2 | 200 | 3 | 300 | 1 | 100 | 600 | 200 | -400 | |||
6 | LEMON | 3 | 300 | 2 | 200 | 1 | 100 | 600 | 100 | -500 | |||
7 | |||||||||||||
8 | |||||||||||||
9 | E.G | ||||||||||||
10 | APPLE | A | 100 | ||||||||||
11 | B | 500 | SHORT -100 | ||||||||||
12 | C | 600 | SHORT -600 | ||||||||||
13 | PRIORITY OF SCHEDULE | PO | |||||||||||
14 | 1 | A | 100 | ||||||||||
15 | 2 | B | 100 | ||||||||||
16 | 3 | C | 100 | ||||||||||
17 | |||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G4:G6 | G4 | =F4*$G$3 |
H4:H6 | H4 | =C4+E4+G4 |
C4:C6 | C4 | =B4*$C$3 |
E4:E6 | E4 | =D4*$E$3 |
J4:J6 | J4 | =I4-H4 |
thanks