Formula For Material Quantities

Nov 18, 2009

I am trying to get a formula for material quantities

What I want to do is

If length is less than 3mts I require 2
If length is more than 3 meters than I require 3 and 1 more for every 3mts after that

e.g:
2mts = 2
3mts = 2
4mts = 3
6mts = 3
7mts = 4
9.1mts = 5 and so on

View 9 Replies


ADVERTISEMENT

Using One Formula To Classify Stock Quantities?

Jan 27, 2014

I have been trying to work with to get my stock table classified into 3 different stock statuses and it comes out, it does not work I have attached the table as it is, the formulae I wanted to use and the end result I would like to have.

View 7 Replies View Related

Formula To Convert Massive Quantities Of Cells Formatted In Hh:mm:ss To Seconds

Apr 1, 2014

Say I have a worksheet that has a column in which every cell is formated in hh:mm:ss format. The column is probably 100 cells long...so from A2 to A101 (A1 is the title of the column). Now, we need to take the data in cells A2:A101 and graph it, but the data needs to be formatted in only seconds. So, in column B, we want to convert the data from column A into seconds format.

For example,

cell A2: 00:01:46
cell B2: 106

Of course, we can't convert each cell by hand, because this needs to be done on a lot of data...worksheets upon worksheets.

View 5 Replies View Related

Multi Level Bill Of Material

Feb 9, 2010

create a multi level BOM in excel:

i have a formula
A=a+b+c+B
B=a+d+e

if i select A, i need excel to give 2a+b+c+d+e (and that should be in another sheet.

also i may take 50% of A +50% of B the resulting formula must appear.

i attached an exemple file.

View 14 Replies View Related

Conditions Used To Take A Length Of Material From Stock

Feb 9, 2010

I have made an Excel illustration to explain what I would like to embark on.

I am not sure of the code, but if there is not an idea, I may see if I can find some idea of the code.

View 14 Replies View Related

Automatically Tracking How Much Material Is Left

Dec 14, 2013

basically I want to be able to keep track of how much vinyl material I have left after each order.

The process would be - When I order a roll of vinyl material I would input the colour ordered and cm ordered by selecting from drop down lists in the 'Vinyl Tracker' sheet. When a customer makes an order, I would select an item, size and colour from drop down lists in the 'Orders' sheet. Depending on what size is selected in the 'Orders' sheet, I would then like Excel to automatically update column 'Cm Remaining' in the 'Vinyl Tracker' sheet, however using the smaller number in the relative size column from 'Item Sizes' sheet.

E.g. If we take the first order:

World Map
Medium
Black - (M)

This would then refer to cell M5 in the 'Item Sizes' sheet (as 43.35 is less than 90).

I would then like the number which is retrieved to be taken away from the relevant cell in the 'Cm Ordered' column, depending on what colour was chosen in the 'Orders' sheet.

To make matters a bit more complex, obviously when any of the numbers in the 'Cm Ordered' column in sheet 'Vinyl Tracker' is 0, I will re-order the same vinyl roll and insert it into the sheet as per usual. How can I make it so that any new orders will take away from the latest instance of the same coloured vinyl?

View 2 Replies View Related

Merge Bill Of Material Columns

Dec 5, 2009

I use CAD software that generates Bills Of Material. I cut & paste these to an Excel template that has column headers in row 3, for example:

U3 = Item name
V3 = Manufacturer
W3 = Reference_item_name
X3 = Reference_item_ID

Starting from row 4, I would like to add the content of columns V, W and X to column U, separated by comma's. No superfluous comma's should be added when columns are empty. It would be nice to have a macro that uses the row 3 column names, so it still works if someone changes the column order.

View 9 Replies View Related

Design A Userform - Add Records Into Material Indent Tab?

Mar 11, 2014

I have a Spreadsheet with various tabs.I want to :-

1.A Userform to add records into "Material Indent"tab.

2.Secondly,transfer rows button on Userform to shift particular rows on entering Reel no. and date to "material Usage"job desired.xlsmtab.

View 6 Replies View Related

VLookup :: Data Based On Material Rank

Oct 29, 2009

I need to sort the material data based on the material rank but i can't use the 'sort/filter' function. Therefore, I used the VLOOPUP function. For some reason the vlookup formula is not working could you let me know what is the problem? see attchment.

View 2 Replies View Related

Conditional Formatting - Find Common Material

Mar 29, 2006

What i am trying to do is to to determine the common material that is
used among different model do product in a product family. I have the
column C the various part number for the product family. Each product
model is made up of different combination of the parts.

In I3:U3 i have the model number for each product. Under each are the
combination of various part that make up each model. What i need to do
is in column G conditional formatiing that if all the different model
use a particular part (part number). The respective cell in column in
the row will be color. This will help me to determine what are the
parts that are common to all the product.

Column C Column G Column I .........................Column U
Part no Common Product 1 Product 2 Product 3 Product 4
12-1234-56 no color 1 4 0 6
13-2345-45 color 2 3 2 2
14-1234-56 no color 0 2 4 2
14-1234-56 no color 0 2 2 2

View 9 Replies View Related

Material Spreadsheet List - Consolidate And Total

Nov 21, 2013

I have a material spreadsheet list that contains multiple entries of the same parts throughtout the sheet. How can i get it to total quanities needed by part numbers and consolidate it to one row instead of multiple rows. quantities are in column c and part numbers are in column d and descriptions in column e.

View 3 Replies View Related

Creating Bill Of Material From Single Table

May 14, 2014

I would like to create Bill of material from single table. I need to select multiple parameters, to expend it till the lowest level, so I will try to explain:

I have one table (ODBC) and there's all data we need. Parent number col A, Item number col B and quantity col C. First level I select main Item number A1234 (this is only thing that I should choose, everything else should be automatically), I get table with all items that are parent A1234 (let's say 10 items). Now I need to look again one level lower in same table for items that have Parent item in list of those 10 items listed earlier (let's say 30 items) and multiply their quantities with quantity of their Parent Item (total qty could be in column D). Then one level lower for items with parent items in those 30 and so on and so on. So when I choose main Item I would like to get table like below (take notice that real table has over a 100.000 items, but I want to show only Bill of material for the main item till the lowest level).

Parent Item
Item
QTY
Total QTY

A1234
B1111
5

[URL] ...

I'm flexible and ok to use VBA, SQL, Excel functions, multiple tables (how to select multiple parameters??)

View 3 Replies View Related

Material Order List: Reduce Waste

Sep 6, 2006

I am reposting this because I do not think I did a very good job explaining what I am trying to do.

I am at a total loss on how (if it is even possible) of how to do this. So what I have done is tryed to break down each step. If someone could even get me started in the right direction many of the steps are redundant and I could work on that part myself.

I am attempt to create system to match parts that are alike in a single project, so I can create a material ordering list. This is just one step in the process (the hardest one) I will take the returned data and use it further in the process to create the actual material list. I have 2 worksheet of Data "PartsNeeded" and "PartsAvailable" with a 3rd sheet "PartsFilled" as my report Sheet.

Attach is the sample data along with a "notes tab"

View 9 Replies View Related

Cross Referencing: Know How Many Of Each Item In The Same Type Of Material And Size, Need To Be Made

Feb 5, 2010

I have a list of products that have individual item numbers that share some common sizes and some common materials and are ordered in different quantities. When it comes to re-ordering the raw material to remake these items, I need to know how many of each item in the same type of material and size, need to be made. The attached spreadsheet shows more clearly what I am probably failing to explain! I have tried various combinations of VLOOKUP combined with MATCH but I can’t get anything to work – I guess I’m getting a little punch drunk here having spent the whole morning trying to find a solution!

View 5 Replies View Related

Multi Level Bill Of Material :: Hide Or Unhide Rows

Jul 24, 2009

Below is the partial copied text of a Multi Level BOM. I am trying to hide or unhide the rows with 4, 5, 6, 7, 8 and greater number of dots. The dots indicate the level of BOM.

Would a macro make the task more automated?

View 9 Replies View Related

Sum Items Quantities From Different Sheets

Apr 17, 2014

I have multiple invoices for ordered products style number and quantity, these invoices are keep adding as orders are coming. I need a separate work book where all items summed. shows style and summed quantity from all invoices.

for example wb1

style quantity
or12 5
or34 2
or45 4

[Code]...

wb3 same style numbers but maybe not in the same column or row, wbs are keep adding as orders are placed.

I need a separate wb

shows style and pluses all quantities

style quantity
or12 6
or34 5
or45 6

View 5 Replies View Related

Sum Quantities Of Product By Year

Dec 27, 2013

I have built a pivot table that shows parts used during a period of years. I need creating a formula that would sum each part by a particular year.

The table is as follows

A part number
B date
C total quantity

I want to display the totals on a separate sheet.

View 7 Replies View Related

Only Show Quantities Of Three Or Greater?

Jan 22, 2014

formula on the attached sheet. I need to update it now. The strengths are going to stay the same, but the weaknesses (purple headers on spreadsheet) only have to show up if the value associated with it is 3 or greater. (So traits with quantities of 1 and 2 will not show up as a weakness)

I have attached a spreadsheet wit desired results.

View 4 Replies View Related

Add Quantities If Data Repeats?

Jul 15, 2014

example attached: If data repeats in column A, then add those corresponding values in column B. Can this be done without VBA?

View 5 Replies View Related

Calculating Daily Quantities

Nov 22, 2009

I have 3 worksheets: Income; Expense; Consolidate.

In the first two sheets i am entering, by dates, quantities that are getting in and out of the warehouse.

My code copies that information in the consolidated sheet.

What I need is to make a code that Calculates the "Daily Quantities" and "Rent", based on quantity in the warehouse, that I am paying each day.

View 7 Replies View Related

Match Quantities Of Buy And Sell

Feb 1, 2008

I want to match the quantities of buy and sell appearing the same line but in different columns based on FIFO. In case of mismatch, the excess quantity should be transferred to a new line below the same column in which there is a mismatch.

View 2 Replies View Related

Totaling Quantities With Dropdown List

Dec 27, 2012

Below I have attached a sheet for keeping track of the number of cables a person produces per day using Drop-Down list. I would like to have a totals area automatically calculate the corresponding cells. I started the Totals area in column AF.

View 13 Replies View Related

Sum A Column When The Quantities Meet Two Criteria

May 28, 2009

I need to sum a column when the quantities meet two criteria. The name in one column, and the date in another column. Probably an easy formula, but new at this and having trouble.

View 4 Replies View Related

Allocating And Carry Over Order Quantities

Jul 16, 2014

I have a sheet which is just a list of product codes and a sum of every order placed for each code. i.e.
Sheet2  AB1Prod codeOrdered2123100345650478960

then I have another sheet which is the orders placed by our customers. These orders are to go out in 6 periods each of 2 weeks.
For various reasons the amount ordered each time won't match the periods.

The sheet beforehand will look like

Sheet1  ABCDEFGHIJKLM1Prod codeP1 REQALLOCP2 REQALLOCP3 REQALLOCP4 REQALLOCP5 REQALLOCP6 REQALLOC212327 20 35 20 12 11 345633 40 50 15 25 11 478915 20 20 20 20 10 

What I need to do is look up the quantity ordered and then allocate the ordered quantity to the periods carrying over the remainder to the next period and so with the table above the result would be

Sheet1  ABCDEFGHIJKLM1Prod codeP1 REQALLOCP2 REQALLOCP3 REQALLOCP4 REQALLOCP5 REQALLOCP6 REQALLOC2123272720203535201812 11 34563333401750 15 25 11 478915152020202020520 10 

Both sheets are sorted by the product code ascending and it doesn't matter if it is a formula or VBA based solution as I am already using both.

The number of product codes is currently just over 400 but will grow to about 550 by the end of period 6.

View 5 Replies View Related

Sum Up The Batch Quantities For A Date With Variance One

Jun 1, 2007

I need to sum up the batch quantities for a date with variance one...
but it doesn't work... I suspect that I'm using wrong formula, it should be not SUMPRODUCT...

when I tried to use just SUM, it adds all the quantities in the colomn.

=SUMPRODUCT(--(($AB$11:$AB$100)=AK12),--($AG$11:$AG$100=1),($AD$11:$AD$100))

View 9 Replies View Related

Distribute Quantities From Known Average Price

Dec 10, 2006

I'm having some problems to find a way to distribute quantities from a known average price. Here's the Example:

My Client bought 100.000 Shares from a Stock, in various prices.

Qtty Price
10041,71
30041,61
70041,78
1.50041,72
4.40041,68
5.50041,75
10.00041,94
13.50041,70
64.00041,80

Then the client wants to distribute this trades for 7 different funds at the same average price.

Qtty Fund 1: 294
Qtty Fund 2: 541
Qtty Fund 3: 1.213
Qtty Fund 4: 13.370
Qtty Fund 5: 16.582
Qtty Fund 6: 28.002
Qtty Fund 7: 39.998

I'm sending an example attached.

View 4 Replies View Related

Multiply Quantities Based On Lookup

Aug 9, 2007

I have a Bill of Materials (BOM) that has been flattened from a database into an Excel format. There are many BOM levels and I need to multiply the quantity of a component by the quantity of the "parent" it reports to. I have attached a sample file with raw data and the expected result (calculated manually). My goal is to create a formula that calculates the "Ext. Qty" column in my expected result. EXT_QTY.xls

Here are some of the rules for the raw data structure:

1. First BOM level at very beginning is always "1"

2. All children of BOM level "1" have a BOM level of "2"

3. All children of BOM level "2" have a BOM level of "3"...

4. There are potentially many BOM levels (approx. 15 levels for full set of data)

5. All of the BOM level "2" items are children of the most recent BOM level "1" assembly and there will be multiple level "1" items, level "2" items, etc.

6. Data is always sequential so the children (and grandchildren) will always come after the parent

View 9 Replies View Related

Calculate Quantities Based On Set Total

Dec 22, 2007

I have three values. The Quantity of items, the cost per items and the cost for the entire order. In some instances I want to give my clients items for free so I need to lower the cost per item in order to keep the entire order cost the same.

In order to ensure we are on the same page let's use the following associations:
Y = Quantity
X = Cost Per Item
Z = Order Total

So essentially I need a formula that will automatically calculate the cost per item (X) when more Quantity (Y) is added in order to keep the order total (Z) the same.

View 5 Replies View Related

Excel 2010 :: Filtering Subtotal Quantities

Sep 6, 2013

I'm using excel 2010.

I'm trying to filter a data set to see only those groups that have a subtotal count of 3 or greater. The subtotals are at each change in customer to count the number of divisions that customer is associated with.

In other words, how can I see a list of only those customers who have the permission to shop 3 or more brands?

Excel Help.xlsx

View 1 Replies View Related

Individually List Grouped Items / Quantities

Jan 15, 2010

I have a list of items and quantities I need of each, such as

cat 3
dog 2
mouse 1
horse 4
snake 2
leopard 4

I'm thinking that it would be a macro that would be way to go for this project.

I want to run a macro that will make the list above the following
cat 1
cat 1
cat 1
dog 1
dog 1
mouse 1
horse 1
horse 1
horse 1
horse 1
snake 1
snake 1
leopard 1
leopard 1
leopard 1
leopard 1

So what happened was that 6 lines of information was transformed into 16 lines of information. My purpose is that I will then will exploring various combination of these items, and thus I think that splitting them up like this will make them more manageable to work with. Since the original list values will change I will not always know how many lines to set aside for the individual breakdown.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved