How to compile material quantities onto one page

[chelsearkemp]

Hello!

This is my first post and I'm really hoping I can get some help! Due to the several non-disclosure agreements I signed on my first day (pretty much saying they get my first born if I slip up) I am going to be using a dummy sheet to explain my problem. I have attached the sheet if you would like to take a look.

So here's my assignment. Create a spreadsheet that will compile all materials used in any given project onto a simple coversheet.

So I have a cover sheet (Cover) that has the quantities of all the different assembly types at the top and below lists all part numbers that are in our database. Then I have sheets for each assembly type (Type 1, Type 2, Type 3) that lists the parts used on each assembly. The qty of the part is the number needed multiplied by the qty of that type.

My question is, is there a way to pull the qty for each part from the Type pages and have them automatically add together on the Cover?

I have a working version that compiles all the part types on another sheet (for example Part A from Type 1, Part A from Type 2, and Part A from Type 3 are all listed next to each other) and then adds up the qty and then the first page references that qty. The problem is I had to manually copy and paste each of these parts on this separate sheet and then manually sum the qty (F1+F2+F3). This isn't very versatile because if the assemblies change or new parts get added I have to manually add them to that compiling sheet.

So what I'm looking for is a way to make excel go into Type 1, see Part A, take the quantity and then go into Type 2, see Part A, take that quantity and add them together on Cover. The problem I'm having is that Part A doesn't necessarily exist on all sheets and then I get errors.

Anyone have any ideas on how to approach this?

Thanks!
Chelsea Parts Test.xlsx

[alansidman]

Here is a VBA solution to compile the data. Once compiled, then create a Pivot Table to summarize by Part.

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button