Macro or formula for tracking raw materials

MartaGH

New Member
Joined
Nov 14, 2018
Messages
4
Hello,

I am not sure if what I want to create is possible but any assistance or guidance in the right direction will be very much appreciated. I am trying to create a spreadsheet that will allow me to track the aluminum and drywall needed in the fabrication of certain frames that we make.

The aluminum comes in bars that are 157.48 inches long. The drywall comes in sheets that are 4ft x 8ft.

What I am trying to achieve:

Two orders comes in, for example: One is for 8 units that are 18"x48" and the other is for 4 units 12"x12".

I have a row for each order. In the "Quantity" column I would input 8 and 4 respectively. In the "Horizontal Side" column, I would input 18" and 12" respectively and in the "Vertical Side" column, I would input 18" and 12".

Now the fun starts: I would like excel to somehow be able to calculate exactly how much aluminum and how much drywall I need to manufacture those frames. Meaning, the system should "know" that to make the 8 18x48 frames I will need 8 bars of aluminum:
- 2 bars are used for the 18" (18*4=144" out of the 157.48" bar and I am left with 2 pieces of scrap that are 13.48" long)
- 6 bars are used for the 48" (15 pieces of 48*3=144" out of the 157.48" bar, and I am left with 5 pieces of scrap that are 13.48" long plus 1 more 48" piece out of the 6th bar and I am left with a bar 109.48" long).

Then, the formula should capture that there are now 7 pieces that are 13.48" long plus one more piece that is 109.48" long so, when the next order gets calculated, it uses up those pieces of aluminum (if they are big enough) before it starts using up a new aluminum bar.

So, for order #2 , I would require a total of 16 12" pieces. The formula would recognize that there are 7 pieces that are 13.48" long that can be used to make 7 of the 16 12" frames, then realize that the remaining 9 pieces can be made out of the 109.48" bar.

Same idea with the drywall piece... the formula should calculate how many of the 8 18"*48" panels can be cut out of the 4ft x 8ft sheets and recognize that the 2 12"x12" may be cut out of the remaining pieces.

With the help of wwbwb I now have a formula that calculates how many bars are needed to manufacture the frames:

=IF([@Quantity]="","",ROUNDUP(([@Quantity]*[@[Inside Panel Horizontal]]*2)/(157.48-ROUNDDOWN(157.48-ROUNDDOWN(157.48/[@[Inside Panel Horizontal]],0)*[@[Inside Panel Horizontal]],0)),0)+ROUNDUP(([@Quantity]*[@[Inside Panel Vertical]]*2)/(157.48-ROUNDDOWN(157.48-ROUNDDOWN(157.48/[@[Inside Panel Vertical]],0)*[@[Inside Panel Vertical]],0)),0)

I have not been able to figure out how to do the rest and I am coming to you for help... Does any of you believe this formula/macro is possible?

Thank you again in advance!!

Marta
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,726
Office Version
  1. 365
Platform
  1. Windows
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

tcardwell

Board Regular
Joined
Dec 22, 2013
Messages
84
Hi MartaGH,

Sounds like what you need is Bill of Material logic that lists all the raw material and labor that will be needed to assemble one unit. If one unit becomes an ingredient in another, higher level assembly then it can be nested inside the higher level BOM. Using Excel tables to accomplish this would be the best approach I think. Do you have any experience with manufacturing software applications that use a bill of material/recipe as a framework to list and organize raw material/ingredients?

TC
 

Watch MrExcel Video

Forum statistics

Threads
1,122,160
Messages
5,594,597
Members
413,916
Latest member
Islandlady

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