I am having to order parts for a system where I can't see what I need to order. I have been trying to pull together a spreadsheet that will query Purchase Order data in our system, shipping schedule spreadsheet, and use a few Bill of Materials and join all of these together to help my in my ordering process ahead of time and maybe with less headaches.
I uploaded an example that I worked up to help illustrate my problem and I am really only missing just a few technical aspects that I can't really figure out.
First let me walk you through the tabs.
Station Assys-The first tab is just a list of assemblies that we make in a particular work station.
Ship Ref - The second tab is technically another spreadsheet that a planner keeps up with for the production of our assemblies.
Open Ord- This is a query on our system to tell me all open order's bought to the station where we make the assemblies.
Need Ord- ** Is where I would like to pull in all the data that is figured through the spreadsheet and tell me what to order and from who. I may have to manually put in all the part numbers used in the Bill of Materials of all the assemblies, but if I could get this sheet to create on the fly I would go that route.
Barebone, Basic, Premium, Elite- All of these tabs are Bills of Material for the assemblies (Currently my spreadsheet at work has these in the same workbook, but I will most likely put all the BOMS in their own workbook).
In the example file I tried to list everything with a comment on the title cell(A-row). To help instead of flipping back and forth. I may also go ahead and note that we used Excel 2003 at work, but I used OpenOffice from home for this example.
http://www.filefactory.com/file/ca51010/n/example.xls
My major issue that I am having is trying to setup the more in depth formulas. In pretty much each case of the spreadsheets where I need info and can't calculate it is because I don't know how to go search through another spreadsheet, find multiple instances of a variable, and pull data from the rows and calculate something with that data.
In Station Assys, I have list the assemblies, in the following column I want the result to be the count of all the assemblies that it finds in shipping schedule with the exception of all the one that are already complete. In the shipping schedule Column B is the complete column. In my spreadhseet at work I have this as a list and exclude all the complete ones already, but I figure that the calculation may not care about the visual data I am looking at and look through everything in the sheet.
Ship Ref and Open Order are fine as is.
We will skip Need order sheet for now and move to the assemblies tabs(Barebones, Basic, etc.) All of the Bill of materials have the same structure, So if I figure it out for one I figure it out for all.
In this tab I have a Bill of material tree that I manually input(fun!).
The column Shelf qty comes from our Inventory counts we do each month.
The column Open Ord needs to search through the Open Order sheet for each part number, find the multiple instances, and add the total on order together.
Column Unit Able takes the shelf qty plus open order qty and devides by BOM qty to tell me how many I can make.
MRP qty is a reference from the first tab that for each assembly.
Qty Need subtracts Units Able from MRP QTY.
Fluff qty is just a number I choose to order up to.
Vendor column searches through the Open Order Sheet for each part number, and returns the vendor numbers(if it find more than one of the same it lists the other.
Now back to the Need Order tab, I would like this spreadsheet to automatically generate from all the data from the Bills of Material, and tell me every part number listed along with it's order information. (For order purposes I would sort this by vendor). I don't want any repeats of the same part number on this sheet.
The Shelf qty in a manual input for our Inventory counts.
The column Open Ord needs to search through the Open Order sheet for each part number, find the multiple instances, and add the total on order together.
MRP QTY searches through all the assemblies and add the multiple instances of the same part number together. (MRP qty is just for reference because there may be exceptions to this giving a skewed data feeling. In example the BOM qty of the screws in each assembly changes, So giving me the MRP qty of the assemblies doesn't correlate to the MRP qty of indivual parts) I may drop this column.
QTY Need searches through all the assemblies and add the qty Need of the multiple Instances. (This is really a more viable in ordering.)
Fluff Qty searches through all the assemblies and add the Fluff of the multiple Instances.
Vendor column is the same as the assembly vendor column.
Used on Column is open for interpretation. I want to to either show the various job numbers this part will be used for, or the assemblies it goes in. Either way it is a reference.
I hope this wasn't too much info at one time, but I would rather know it all at once instead of one step at a time. I really hope the data I need is easy to formulate. This should really help me out. This example was cut way down. I actually have 6 stations, that I have about 250 parts in the bills of material in each station, and each station has about 15-20 assemblies. It makes my brain hurt to put all this together every time I want to see how I am doing on my purchase order's vs. sales. And my boss likes to tell me to do this at random even after I have already done it that day.
Thank you for all the help and suggestions.
I uploaded an example that I worked up to help illustrate my problem and I am really only missing just a few technical aspects that I can't really figure out.
First let me walk you through the tabs.
Station Assys-The first tab is just a list of assemblies that we make in a particular work station.
Ship Ref - The second tab is technically another spreadsheet that a planner keeps up with for the production of our assemblies.
Open Ord- This is a query on our system to tell me all open order's bought to the station where we make the assemblies.
Need Ord- ** Is where I would like to pull in all the data that is figured through the spreadsheet and tell me what to order and from who. I may have to manually put in all the part numbers used in the Bill of Materials of all the assemblies, but if I could get this sheet to create on the fly I would go that route.
Barebone, Basic, Premium, Elite- All of these tabs are Bills of Material for the assemblies (Currently my spreadsheet at work has these in the same workbook, but I will most likely put all the BOMS in their own workbook).
In the example file I tried to list everything with a comment on the title cell(A-row). To help instead of flipping back and forth. I may also go ahead and note that we used Excel 2003 at work, but I used OpenOffice from home for this example.
http://www.filefactory.com/file/ca51010/n/example.xls
My major issue that I am having is trying to setup the more in depth formulas. In pretty much each case of the spreadsheets where I need info and can't calculate it is because I don't know how to go search through another spreadsheet, find multiple instances of a variable, and pull data from the rows and calculate something with that data.
In Station Assys, I have list the assemblies, in the following column I want the result to be the count of all the assemblies that it finds in shipping schedule with the exception of all the one that are already complete. In the shipping schedule Column B is the complete column. In my spreadhseet at work I have this as a list and exclude all the complete ones already, but I figure that the calculation may not care about the visual data I am looking at and look through everything in the sheet.
Ship Ref and Open Order are fine as is.
We will skip Need order sheet for now and move to the assemblies tabs(Barebones, Basic, etc.) All of the Bill of materials have the same structure, So if I figure it out for one I figure it out for all.
In this tab I have a Bill of material tree that I manually input(fun!).
The column Shelf qty comes from our Inventory counts we do each month.
The column Open Ord needs to search through the Open Order sheet for each part number, find the multiple instances, and add the total on order together.
Column Unit Able takes the shelf qty plus open order qty and devides by BOM qty to tell me how many I can make.
MRP qty is a reference from the first tab that for each assembly.
Qty Need subtracts Units Able from MRP QTY.
Fluff qty is just a number I choose to order up to.
Vendor column searches through the Open Order Sheet for each part number, and returns the vendor numbers(if it find more than one of the same it lists the other.
Now back to the Need Order tab, I would like this spreadsheet to automatically generate from all the data from the Bills of Material, and tell me every part number listed along with it's order information. (For order purposes I would sort this by vendor). I don't want any repeats of the same part number on this sheet.
The Shelf qty in a manual input for our Inventory counts.
The column Open Ord needs to search through the Open Order sheet for each part number, find the multiple instances, and add the total on order together.
MRP QTY searches through all the assemblies and add the multiple instances of the same part number together. (MRP qty is just for reference because there may be exceptions to this giving a skewed data feeling. In example the BOM qty of the screws in each assembly changes, So giving me the MRP qty of the assemblies doesn't correlate to the MRP qty of indivual parts) I may drop this column.
QTY Need searches through all the assemblies and add the qty Need of the multiple Instances. (This is really a more viable in ordering.)
Fluff Qty searches through all the assemblies and add the Fluff of the multiple Instances.
Vendor column is the same as the assembly vendor column.
Used on Column is open for interpretation. I want to to either show the various job numbers this part will be used for, or the assemblies it goes in. Either way it is a reference.
I hope this wasn't too much info at one time, but I would rather know it all at once instead of one step at a time. I really hope the data I need is easy to formulate. This should really help me out. This example was cut way down. I actually have 6 stations, that I have about 250 parts in the bills of material in each station, and each station has about 15-20 assemblies. It makes my brain hurt to put all this together every time I want to see how I am doing on my purchase order's vs. sales. And my boss likes to tell me to do this at random even after I have already done it that day.
Thank you for all the help and suggestions.
