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.
I am trying to put all my parts with quantities on a seperate sheet called "Parts List" Every time you select a quanity for one of the parts, I want it to pop up on my parts list. This will make it easier to identify the exact parts I want and also the quantity I need. This will be much more convenient then scrolling down my parts list and trying to find the one's with quantities.
I think I need to use a vlookup or even a Macro but I don't know how to go about doing this.
What's the functions can figure out on the below conditions, For example :
PRODUCTSYEARQTY
AAA 2015 2
ABB 2011 3
AAA 2013 8
BBA 2011 6
AAA 2011 4
BCC 2014 1
Conditions:
1) looking for AAA product
2) sum the qty is before 2014
The answer is :12
I am trying to use a PRODUCT formula to return the product of a list of numbers in a column, between 2 specified dates. The spreadsheet is arranged as follows:
Column BA list of dates
Column C & DNumerical data not used in any calculations
Column EThe numbers to multiply together
Cell A1Start date to be used in the PRODUCTformula
Cell A2End date to be used in the PRODUCT formula
The formula I have to multiply the data in column E, subject to the start and end dates in Column A is:
{=PRODUCT(IF(B15:B1000>A1,IF(B15:B1000<=A2,INDEX(B15:E1000,0,4))))}
This returns zero though works appropriately if I replace “PRODUCT” with a “SUM”.
I'm working on a order spreadsheet system, and I have one sheet called Product Details, where the product name, list price and product code are found, these link to the Sales order page, and I need them so that they can be added to.
So far, I have created a dynamic named range for the Product name on the Product details sheet, and linked the the range via a list validation on the Sales order sheet.
The drop down list displays the products and can be added to by typing new product names on the other sheet.
What I need now is that when a product on the sales order page is selected, it draws the the list price and product code data automatically from the sheet, I tried using a normal vlookup, but I couldn't get it to work. I also need the list price and product code columns to be 'dynamic' so new values can be added further along.
I would like to have a Macro to go from the first tab called "Start" and end up with the second tab "End" automatically. BAsically I need to take the product on each line under Tab Start and reproduce it for sizes 35-41 always ending with "-(size)". Then this new Product with Size needs to be multiplied one below the other for as many times as I have pictures (number shown under column B of the tab Start). Next to this value in column B of tab End I'd like the same name listed again but with the number 1, then 2, then 3, etc. as needed and the extension .jpg.
View 4 Replies View Relatedi need to look up a product based on the number and the qty. see the example attached.
On tab "Printer - S" 1 need to lokup the costs based on the product code 8 and the qty 100. How do i do this and make it adjustable for any range of priduct codes and qtys. Again, becuase this will be used on a Mac Office, i cannot use macros.
In the first sheet I have two columns, one for the product_id and one for the name of the product. So the Sheet1 is like a small database. The second sheet is for the orders.What I want is when I type the product id in the A column of the orders sheet(Sheet2) to auto insert the product name in the B column so i dont have to write it every time.
View 2 Replies View RelatedI'm trying to create a supply order form. Is it possible to enter a product name in one cell and have the price for that product automatically appear in another cell? Is there a formula that I can use to make this happen?
View 2 Replies View RelatedI 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
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.
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 RelatedI 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.
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
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 RelatedI have two combo boxes: One for entering the Year, and one for the month. I can produce a message if the user leaves either box blank but I want a message to apear it the user selects a year AND month less than the current year (iYear) and current month (iMonth). I therefore need an AND statement between the two criteria but i dont know how to do it.
'....First Checks the Comboboxes arent blank then below Checks a future month/year secection is chosen
ElseIf YearBox.Value = iYear & iMonthbox < iMonth Then
MsgBox ("You may not enter Data before the current Month")
Else '...... Run main code here
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 RelatedBelow 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 RelatedI 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 RelatedI 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.
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))
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.
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
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.
Looking for a formula to compare current year values to previous year values. For example, if the current year has values for the month of January through March (100, 100 and 150), current year value will be 350 and the previous year value will be 975 (i.e. 300+275+400). The aim here is to make the previous year months summation equal to the present (or current) values. As new values are entered for the current year, the previous year's values will have to change to reflect the new month's value entered for the current year.
Month 2012 2013
Jan 300 100
Feb 275 100
Mar 400 150
April 650
May 454
June 800
July 500
Aug 375
Sep 525
Oct. 300
Nov 410
Dec 510
Sample file is attached : Comparison_Years.2011.xls‎
I am working on a budget for myself and want it to have running dates so the first data column will have the current month. I was able to succeed with this using the EOMONTH function followed by EDATE functions in the following cells, I then have these columns filled using a nested VLOOKUP MATCH function pair.
The problem I run into is with the months that extend into the next year, in my data table I have month by month listed started on 01/01/2014 ending 12/01/2014 but as soon as the month is no longer January the last column in my budget cannot find the information needed due to it looking for 2015. so what I would like to know is if there is a way to make the data table change the year to the following year after today is beyond that month, so for example on March 1 2014 both January and February would be changed to 2015.
Attached is an example : Budget Example.xlsx‎
I have dates in my column “A”, for example (A1 cell =22-Mar-1971), (A2 cell=30-Dec-1965). Now my requirement is in B column date and month from A column and year should take current year. Output in B column (B1 cell =22-Mar-2009), (B2 cell=30-Dec-2009)
View 3 Replies View RelatedI'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
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.
I want to allocate the Quantities which is available in DC based on Sales, Store OH and In Multiples of Pallet Rounding (PR).
For Eg. If I have 200 Qtys in DC, Excel should allocate these 200 Qty's in multiples of PR to highest selling stores and also consider the store On Hand.
Below is the Format
Article #
Store #
Region
PR
Model
[Code]...