douglascaixeta
New Member
- Joined
- Mar 21, 2009
- Messages
- 45
- Office Version
- 2019
- Platform
- Windows
Hi!
I have a sheet called Recipes Table. Contains all necessary resources to produce something. Then I setup what is needed to produce, in this case Calc v4 and Pocket One I.
The table contains everything I need. But I need to know which approach I should use to gather the info from this table and put in the Production Plan sheet to discover how much of each component is necessary.
Its market in RED (column E, of Production Plan) what I mean. This is the column that I would like to automate.
Using: MS Office 2019
THE COLUMN E, is missing the comments. Would be much easier to post the workbook, but since is against the rules, lets do it the hard way.
The comments on the values of COLUMN E is essential to understand what I mean.
So take Plastics, value 65 on E6. This is how I manually got to this value:
- 4 plastic parts to produce 1 logic circuit x 13 logic circuits needed = 52
- 1 plastic parts to produce 1 enhanced battery cell x 13 enhanced battery cell needed = 13
- Total: 52 + 13 = 65 plastic parts needed
Circuit board, on E9:
- 1 circuit board to produce 1 "logic circuit" x 13 logic circuit needed = 13
- 3 circuit board to produce 1 "7 segment display" x 8 "7 segment display" needed = 24
- Total: 24 + 13 = 37 circuit board to produce
Integrated Circuit on E11:
- 5 integrated circuit to produce 1 "7 segment display" x 8 "7 segment display" needed = 35
- Total: 35 integrated circuit to produce
Plastic on E13:
- Plastic Parts: 4*65 = 260
- Circuit Board: 2*37 = 74
- Plastic Case: 4*8 = 32
- Total: 366
Metal on E14:
- Circuit Board: 2*37 = 74
- Enhanced Baterry Cell: 1*13 = 13
- Total: 87
Electronic Parts on E15:
- Integrated Circuit: 25*35 = 875
- Logic Circuit: 8*13 = 104
- Total: 979
Chemicals on E16:
- Enhanced Battery Cell: 2*13 = 26
So as you can see, its just manual SUM made by using the first table that contains all numbers.
I setup on E3 and E4 how many "Calc v4" and how many "Pocket One I" I need to produce and then manually look at the necessary components on the table and sum the resources.
I have a sheet called Recipes Table. Contains all necessary resources to produce something. Then I setup what is needed to produce, in this case Calc v4 and Pocket One I.
The table contains everything I need. But I need to know which approach I should use to gather the info from this table and put in the Production Plan sheet to discover how much of each component is necessary.
Its market in RED (column E, of Production Plan) what I mean. This is the column that I would like to automate.
Using: MS Office 2019
Good Company.xlsx | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | Materials Needed / Modules to Produce | Plastic Parts | Circuit Board | Integrated Circuit | Plastic Case | Enhanced Battery Cell | Logic Circuit | 7 Segment Display | Calc V4 | Pocket One I | ||
2 | Plastic | 4 | 2 | 0 | 4 | 0 | 0 | 0 | 0 | 0 | ||
3 | Metal | 0 | 2 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | ||
4 | Electronic Parts | 0 | 0 | 25 | 0 | 0 | 8 | 0 | 0 | 0 | ||
5 | Chemicals | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 0 | 0 | ||
6 | Glass | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
7 | Wood | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
8 | Plastic Parts | 0 | 0 | 5 | 3 | 1 | 4 | 0 | 0 | 0 | ||
9 | Circuit Board | 0 | 0 | 0 | 0 | 0 | 1 | 3 | 0 | 0 | ||
10 | Integrated Circuit | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 0 | 0 | ||
11 | Plastic Case | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | ||
12 | Enhanced Battery Cell | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 1 | ||
13 | Logic Circuit | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 1 | ||
14 | 7 Segment Display | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | ||
Recipes Table |
Good Company.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | NEED TO AUTOMATE THIS COLUMN | ||||||||
2 | Crafiting duration | Quantity Output | Daily Production | Needed Production | Tables Needed | Week Demand | |||
3 | Calc v4 | 1,1 | 1 | 0,91 | 4 | 4,4 | 33 | ||
4 | Pocket One I | 1,1 | 1 | 0,91 | 2 | 2,2 | 16 | ||
5 | |||||||||
6 | Plastic Parts | 0,6 | 10 | 16,67 | 65,0 | 3,9 | |||
7 | Plastic Case | 0,6 | 1 | 1,67 | 6 | 3,6 | |||
8 | Enhanced Battery Cell | 0,6 | 1 | 1,67 | 10 | 6 | |||
9 | Circuit Board | 0,6 | 5 | 8,33 | 37 | 4,44 | |||
10 | Logic Circuit | 0,6 | 1 | 1,67 | 10 | 6 | |||
11 | Integrated Circuit | 0,6 | 10 | 16,67 | 35 | 2,1 | |||
12 | 7 Segment Circuit | 0,6 | 1 | 1,67 | 6 | 3,6 | |||
13 | Plastic | 1 | 50 | 50 | 366 | 7,32 | |||
14 | Metal | 1 | 50 | 50 | 87 | 1,74 | |||
15 | Electronic Parts | 1 | 100 | 100 | 979 | 9,79 | |||
16 | Chemicals | 1 | 50 | 50 | 26 | 0,52 | |||
Production Plan |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F6:F16,D6:D16,D3:D4,F3:F4 | D3 | =C3/B3 |
E3:E4 | E3 | =ROUNDDOWN(G3/7,0) |
E7:E8 | E7 | =Assembly!G8 |
E10 | E10 | =Assembly!G10 |
E12 | E12 | =Assembly!G11 |
THE COLUMN E, is missing the comments. Would be much easier to post the workbook, but since is against the rules, lets do it the hard way.
The comments on the values of COLUMN E is essential to understand what I mean.
So take Plastics, value 65 on E6. This is how I manually got to this value:
- 4 plastic parts to produce 1 logic circuit x 13 logic circuits needed = 52
- 1 plastic parts to produce 1 enhanced battery cell x 13 enhanced battery cell needed = 13
- Total: 52 + 13 = 65 plastic parts needed
Circuit board, on E9:
- 1 circuit board to produce 1 "logic circuit" x 13 logic circuit needed = 13
- 3 circuit board to produce 1 "7 segment display" x 8 "7 segment display" needed = 24
- Total: 24 + 13 = 37 circuit board to produce
Integrated Circuit on E11:
- 5 integrated circuit to produce 1 "7 segment display" x 8 "7 segment display" needed = 35
- Total: 35 integrated circuit to produce
Plastic on E13:
- Plastic Parts: 4*65 = 260
- Circuit Board: 2*37 = 74
- Plastic Case: 4*8 = 32
- Total: 366
Metal on E14:
- Circuit Board: 2*37 = 74
- Enhanced Baterry Cell: 1*13 = 13
- Total: 87
Electronic Parts on E15:
- Integrated Circuit: 25*35 = 875
- Logic Circuit: 8*13 = 104
- Total: 979
Chemicals on E16:
- Enhanced Battery Cell: 2*13 = 26
So as you can see, its just manual SUM made by using the first table that contains all numbers.
I setup on E3 and E4 how many "Calc v4" and how many "Pocket One I" I need to produce and then manually look at the necessary components on the table and sum the resources.
Last edited: