Excel Multi sheets & work book help

CaseyDoes

New Member
Joined
Mar 27, 2011
Messages
10
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.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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.

I almost thought I had it using Vlookup, but it was just giving me the first result it found. I would like to find all the results and do something with them.
 
Upvote 0
OK I think that you will need to use VBA code to do this.

Can you give a simple example like find Fred in Sheet1 Column A and copy the entire row to the bottom of Sheet2.
 
Upvote 0
On Sheet one I have my list of things to find.

Alpha
Beta
Gamma

On Sheet two I have a list of the variables and I want to count them and put them in the second column of Sheet one.

Alpha
Alpha
Gamma
Beta
Alpha
Gamma
Gamma

My result on Sheet 1 would be
Alpha 3
Beta 1
Gamma 3
 
Upvote 0
Try like this - formula in B1 copied down

Excel Workbook
AB
1Alpha3
2Beta1
3Gamma3
Sheet1




VBA is Visual Basic for Applications, a programming language, and is used when it can't be done using a formula.
 
Upvote 0
Sweet.

Now let's add one more thing in it.

Let say it counts them but only if it doesnt equal 'C' in another column

<table border="0" cellspacing="0" cols="2" frame="VOID" rules="NONE"> <colgroup><col width="86"><col width="86"></colgroup> <tbody> <tr> <td style="border: 1px solid rgb(0, 0, 0);" width="86" align="LEFT" height="17">Alpha</td> <td style="border: 1px solid rgb(0, 0, 0);" width="86" align="LEFT">c</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Alpha</td> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT">c</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Gamma</td> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT">c</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Beta</td> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT">
</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Alpha</td> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT">
</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Gamma</td> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT">
</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Gamma </td> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT">
</td> </tr> </tbody> </table>

I was trying this:

=COUNTIF(Sheet2.$A$1:$A$99;A1 & Sheet2.B1:B7 != 'c')

but that wasn't working...
 
Upvote 0
Try

=COUNTIF(Sheet2.$A$1:$A$99;A1 & Sheet2.B1:B7 !<> "Cal

but I think you'll need to count them separately then add them.
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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