Approach on how to pull data from a table and sum

douglascaixeta

New Member
Joined
Mar 21, 2009
Messages
45
Office Version
  1. 2019
Platform
  1. 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

Good Company.xlsx
ABCDEFGHIJ
1Materials Needed / Modules to ProducePlastic PartsCircuit BoardIntegrated CircuitPlastic CaseEnhanced Battery CellLogic Circuit7 Segment DisplayCalc V4Pocket One I
2Plastic420400000
3Metal020010000
4Electronic Parts0025008000
5Chemicals000020000
6Glass000000000
7Wood000000000
8Plastic Parts005314000
9Circuit Board000001300
10Integrated Circuit000000500
11Plastic Case000000011
12Enhanced Battery Cell000000021
13Logic Circuit000000021
147 Segment Display000000011
Recipes Table



Good Company.xlsx
ABCDEFG
1NEED TO AUTOMATE THIS COLUMN
2Crafiting durationQuantity OutputDaily ProductionNeeded ProductionTables NeededWeek Demand
3Calc v41,110,9144,433
4Pocket One I1,110,9122,216
5
6Plastic Parts0,61016,6765,03,9
7Plastic Case0,611,6763,6
8Enhanced Battery Cell0,611,67106
9Circuit Board0,658,33374,44
10Logic Circuit0,611,67106
11Integrated Circuit0,61016,67352,1
127 Segment Circuit0,611,6763,6
13Plastic150503667,32
14Metal15050871,74
15Electronic Parts11001009799,79
16Chemicals15050260,52
Production Plan
Cell Formulas
RangeFormula
F6:F16,D6:D16,D3:D4,F3:F4D3=C3/B3
E3:E4E3=ROUNDDOWN(G3/7,0)
E7:E8E7=Assembly!G8
E10E10=Assembly!G10
E12E12=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:

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Auto Pull and multiple
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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
Back
Top