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.
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Am I posting this in the wrong section?


No but you are asking a 'really big' question that you would normally pay a consultant to answer.

Try asking a series of 'small' questions, one at a time.
 

CaseyDoes

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

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

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.
 

CaseyDoes

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

CaseyDoes

New Member
Joined
Mar 27, 2011
Messages
10

ADVERTISEMENT

Thought I might as well go ahead and say I don't know what VBA is. :(
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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.
 

CaseyDoes

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

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,348
Messages
5,528,184
Members
409,807
Latest member
nicky736

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top