Google Spreadsheet Post #824
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Oct 24, 2012
user Auric said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/aI8HEGwojT8)
Parts Assembly Inventory
OK, I thought this would be simple, but it's quickly becoming a monster - I'll try to give good examples. I have searched and found nothing useful, so I'm at a loss here. I can't believe nobody has ever needed to do this!
So if you have a list of qty and assemblies, and multiple columns after each that list parts of the assemblies. You would want to multiply the qty by the number of parts needed to get a sum at the bottom of each column of parts that would tell you how many of that part you needed to assemble the required qty.
What I am using now for the total cells at the bottom of each column is:
=sum($B2*C2)+sum($B3*C3)+sum($
I have over 50 assemblies, so this will get quite huge
I have a small example sheet here : https://docs.google.com/
You can see how I built the above formula in cell C9 and then dragged it down the row to fill in the other columns' totals.
In my example, I need 4 widgets, and each one requires 2 1" bars and 2 2" hex bolts, so to make 4 widgets I would need 8 bars and 8 bolts.
At the bottom of each column of parts is the total number of that part required to make the list of assemblies.
The current method works, but is crude and makes for very very long formulas that are time consuming to edit. Is there a better way?
Thanks in advance for any light you may be able to shine on this issue!
So if you have a list of qty and assemblies, and multiple columns after each that list parts of the assemblies. You would want to multiply the qty by the number of parts needed to get a sum at the bottom of each column of parts that would tell you how many of that part you needed to assemble the required qty.
What I am using now for the total cells at the bottom of each column is:
=sum($B2*C2)+sum($B3*C3)+sum($
I have over 50 assemblies, so this will get quite huge
I have a small example sheet here : https://docs.google.com/
You can see how I built the above formula in cell C9 and then dragged it down the row to fill in the other columns' totals.
In my example, I need 4 widgets, and each one requires 2 1" bars and 2 2" hex bolts, so to make 4 widgets I would need 8 bars and 8 bolts.
At the bottom of each column of parts is the total number of that part required to make the list of assemblies.
The current method works, but is crude and makes for very very long formulas that are time consuming to edit. Is there a better way?
Thanks in advance for any light you may be able to shine on this issue!
----------------------------------------------------------------------------
following is a solution to the problem
in my solution I have moved the Totals required to the top, and in the bargain I only need 1 formula per column
No comments:
Post a Comment