Stock Remarks Depending On Quantity
Jun 13, 2014Find attached formula on b2 , assume some numbers on a1
stk quantity remarks.xls‎
ADVERTISEMENT
Automatically Adjust Stock Quantity As Materials Are Used Or Added?
Mar 3, 2013i am trying to build a spread sheet showing materials in a stores tools, materials,and usage ect. i have put all the formulas in for example cell c =sum(b2-a2) to get a remaining stock left after usage but what i need to know is a way to add more new stock onto the sheet because every time i go to add on new stock it just resets itself is their anyway of doing it so that when you add new stock to cell a for example it automatically adds on to the total in cell c??
View 1 Replies View RelatedDiscount Prices Based On Order Quantity And Package Quantity
Nov 23, 2009I have two idential spreadsheets with several rows of items each with different packaging quantities, price break quantities and corrisponding prices next to each break quantity. I'm trying to build a discounting sheet on an idential page by using formulas that read off the price sell in the same relative position on sheet 2. Below is one row of sheet1 and the idential row of sheet2 - underneath the cells are the rules I would like incorporate:
Sheet 1
ABCDEFG
1Pack QtyQty 1Prc 1Qty 2Prc 2Qty 3Prc 3
210010.91000.744000.6
Sheet 2
ABCDEFG
1Pack QtyQty 1Prc 1Qty 2Prc 2Qty 3Prc 3
210010.91000.744000.6...........................
Cell Update With Real Time Stock Quotes For Particular Stock
Aug 7, 2012What is the easiest way to have a cell update with real time stock quotes for a particular stock?
View 4 Replies View RelatedStock Order Sheet To Be Created From Stock Inventory
Jan 8, 2014I have created a stock sheet on Sheet1, i have say 100 items and each item has a min and max stock order. Once the item hits a min low, the last cell (O) will display an order needs to be placed.
I was wondering if there way a way that on sheet2, it can calculate all the data on sheet1 and if there is stock that needs to be ordered, it will appear on sheet2?
This way i can just print sheet2 and send it to the supplier without having the entire 100 items displayed -if it does not need to be ordered?
VBA To Auto Update The Remarks
May 9, 2014Auto Updating the Comments in Column "M" based on the Values.
I had a TAT Report where there Two different Types i.e., OBI & OBC in Column "D".
In Column "L" i had a overall Production TAT (TAT - Turn Around Time).
Now i need to Auto update the Column "M" using below Criteria's.
1. Type : OBI
If the Production TAT is less than or equal to 3 hours then i need Auto Comment as "Completed"
If the Production TAT is Greater than 3 hours then i need Auto Comment as "Exceeded due to Neglegency"
2. Type : OBC
If the Production TAT is less than or equal to 10 hours then i need Auto Comment as "Completed"
If the Production TAT is Greater than 10 hours then i need Auto Comment as "Exceeded due to Neglegency"
Hide The Entire Row If The Remarks Is CLOSED?
May 7, 2014I got this sheet wherein i needed to hide the entire row when i print it if the REMARKS was already closed.
View 2 Replies View RelatedUsing Conditional Formatting To Find The Respective Remarks
Nov 14, 2008Anyone can help me? I trying to give remark to my Profits and Loss amount with Poor, Good, and Excellent.
For example if my amount is more than $10000 it should be remark Excellent automatically by excel in the cell beside. How am I able to do this with conditional formatting in Excel 2007.....
Insert Formula Depending On Cells Depending Of Variables?
Jan 27, 2014 In a macro I want to automatically insert a formula in several cells. The formula depends on other cells, and I want to be able to manipulate these cells with variables. Here is my code:
[Code] .....
All my variables are declared:
Sheets("DATOS YTD 2014").Cells(I, Semana + 1) is the cell I want to be able to chose due to the variables
Sheets("DATOS YTD 2014").Cells(I, 3) is the reference cell for the VLOOKUP formula, that will vary with I
SheetMonth.Range("B3:W172") is a range in the sheet SheetMonth which is fixed
The error message I receive is the following: "Run-time error 13, Type mismatch"
I precise that the next step is to make the ",6," part in the vlookup formula also variable... but I guess once my current issue is solved this one will be a piece of cake.
Split Quantity Onto One Row Each?
Mar 30, 2014I have a table like this:
QTY PRODUCT
---------------------
1 AB111
3 CC332
2 DA221
Is there a way to display that information on another sheet so that each individual product is on its own line with a QTY of one? I can do it with VBA but was wondering if there is an alternative?
QTY PRODUCT
---------------------
1 AB111
1 CC332
1 CC332
1 CC332
1 DA221
1 DA221
Sum Quantity Of First Column
Oct 6, 2009I have 3 columns:
in first cell (A1) i have a 553482300040
in second cell (B1) quantity: 1000
and in third cell (C1) i have first 2 numbers from first cell (A1), in that case 55.
In second row, first cell (A2) i have another code, but begins with 55 too, in second cell (B2) i have quantity for this: 500.
Using IF AND To Track Quantity Totals
Jun 10, 2014I am currently trying to find a better way to track quantities of delivered material from multiple suppliers. I have a attached a sample of what I am trying to do. In the "totals" sheet Row 3 is working as planned. There are a couple of issues that I am having with my formatting as is. First off in my "raw" data sheet there is an empty row between each row of numbers (this is they way I am sent the information from the supplier). This empty row causes my totals to place a Q everywhere column A on "Raw" is not filled with "B". Is there a way to have the totals sheet only pull from the cells with values in them?
My other question is how I would be able to get the values for material, Net wt., and Charges to populate if Column A in "Raw" is Q?
Quantity Tracking.xlsx
Extract Data Quantity Less Than 40
May 13, 2014I have data in ms excel and in column "F" is the Qty
If the Qty is Less then 40 then extract the data and put into the sheet "Less then 40"
I have attached sample excel file : Extr.xls‎
Total Quantity Calculation
Jun 18, 2009I have a INDENTED list from a cad software that looks like this.
It gives me the quantaty of each position. For instant in the list
I have 5 of 1.1 but because 1.1 is part of 1 makes my total 3x5=15
I have 2 of 1.1.1 but because 1.1.1 is part of 1.1 which again is part of 1 makes the total 2x5x3=30
POS QTY Total QTY
1 3 3 (=3)
1.1 5 15 (=3x5)
1.1.1 2 30 (=2x5x3)
1.2 2 6 (=2x3)
1.3 1 3 (=3x1)
2 1
My question how to write a formula that automatically calculates the total quantity.
Print Quantity As Formula
Mar 9, 2007I want to have the print quantity to be the following:
textbox1 divided by =VLOOKUP(textbox2,Kits!$B$3:$F$25030,3,FALSE)
I need to really do the following:
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:= "formula = textbox2/VLOOKUP(textbox2,Kits!$B$3:$F$25030,3,FALSE)", Collate _
:=True
Cumulative Value From Quantity Lookup
Apr 24, 2008I'm guessing it will be some sort of nested loop but I'm just going round in circles trying to solve it. I need to calculate a cumulative value based on the following criteria:
1. Pre Qty - can be any integer value
2. Qty - can also be any integer value
Accumulate values by looking up the Pre Qty + 1 then add all quantities * values based on those provided in a table giving different to - from ranges. Attached is a spreadsheet
COUNTIF To Tabulate Corresponding Quantity In Column E
Jul 12, 2014I have 44 cells each in columns D & E, column D being a class of item, and E being the quantity of that class, and I want cell G42 to show the total quantity of the class. I have used =COUNTIF(D5:D49,"A") and am only showing the number of A's (A being the class) in column D. What formula can I nest in this equation to get my desired results?
View 4 Replies View RelatedSUM Cells With Same Content And Multiplied By Quantity Of Each?
Jan 8, 2014Basically i need a formula that will look at the message content on a seperate sheet (Data sheet on the attached example) and multiple by the volume of the message type to return a value on a seperate sheet.
The problem i have is our messages appear in the data as seperate lines based on the entire message content, with a quantity of how many times that exact message was sent on a day DESPITE there being many variations of a message, they may all only equate to 1 or 2 actual message types.
In the example you will see the Data tab showing Example message 1, Example message 1.1 etc. All of the Example message 1-1.5 are actually the same message type (same applies to Example message 2) and i need to be able to have formula that groups and counts these to return a value by message type * by the quantity per day. So in the example, Example message 1 would return a result of 10 on the main sheet.
Summarise A Varying Quantity Of Worksheets?
Sep 29, 2008I have monthly workbooks which each have 5 weekly sheets and one summary sheet. I intend to copy the summary sheets to a new "total period analysis" workbook.
In this second annual workbook I intend to total all the monthly sheets together then analyse it. BUT as the books for the months to come haven't been written yet, I can't include them in my formulas. How can I write formulas to include the sheets that WILL be included without getting #ref errors? Can it be done. Or, is there a better way to do this? Some VBA to add the new sheets to the formulas maybe? I don't know VBA.
Calculate The Quantity Of Each Size Of An Item
Nov 26, 2008I need a formula that will calculate the quantity of each size of an item (XS,S,M,L,XL). For example to calculate the quantity for Mediums, I need the formula to look at column C, if M is entered, then look at the adjacent cell in column A for the quantity and formulate a total number (from rows 4-21) and enter the total quantity in C27. See attachment.
View 2 Replies View RelatedCalculate Price Based On Quantity
Apr 21, 2009I`m relatively inexperienced with Excel, searched for a few days and have not been able to figure out how to calcuate this value. I have multiple worksheets to work out financials. I have a separate worksheet with the pricing breakdown based on volume. 2 columns
0 - 99,999 0.05
100,000 - 499,999 0.04
500,000 - 999,999 0.03
1,000,000+ 0.03
On a separate worksheet I want to add I6+I7, depending on the value multiply it by the value in the second column. So if the total is 520,000 then multiply it by 0.03.
Printing PDF Files Quantity In Excel?
Apr 24, 2014So i have working printing macro code in excel that sends files to selected printer based on last 3 characters, when i click a button only 1 file per click is send to printer but i want to send more files (quantity is listed in row D). I think i need to add quantity row to shell command for printing so it will send not only name of pdf file but quaintity too:
[Code]....
Here is a rough look in excel and rows : PDF.jpg
And here is whole code:
[Code] ......
Calculate Two Different Prices Based On A Given Quantity
Aug 29, 2006I want to be able to calculate a price based on a given quantity. But here is the trick.
Example: Widget comes in cases of 12 at $1.00 per case. If the customer orders 36 widgets, then the cost is 36 x $1.00.
If item A is ordered in quantities less than "12" than there is a 15% upcharge.
Example: Widget comes in cases of 12 at $1.00 per case. If the customer orders 4 widgets then the price is 4 x $1.15
But, if the customer orders 35. Then the first 24 are calculated at 24 x $1.00 and the remaining 11 are calculated at 11 x $1.15.
Formula To Suggest Purchase Quantity
Apr 22, 2009I am trying to enter a formula that will automatically suggest a purchase quantity by comparing the pieces needed against the required package quantity. I have attached a small example workbook, which has a description of the requirements for the formula, but I will also incude that description in this post.
Problem Description:
Our purchasing guideline is that we MUST order product in package quantities, as the vendor won't ship a partial package. The required package quantity for each part is shown in column B. If the pcs needed qty in column A is less than half the buy pack qty, you buy zero. If it is half or above, you buy a full package.
I want to put a formula in column C that will recommend the suggested buy quantity. The formula would need to compare the pcs needed to the buy pack qty, and put the suggested buy qty in column C. It should also consider multiples of the buy pack qty, such as if the buy pack is 5, and the pcs needed is 12, the formula would suggest 10 pcs. If the buy pack is 5, and pcs needed is 13, the formula would suggest 15 pieces, etc. I have manually entered the sugg buy qty for the first 22 rows, to give examples of what value the formula should return.
Looking Up Items / Quantity Within Dates And Times?
Jul 13, 2013I get a list of data through xml data import
column A is dates WITH time, appears like so: 2013-07-13 20:25:02
column B is the product name
column C is the quantity
now I want to search certain items sold between two dates AND times which will then give me the total amount of that item sold between those dates and times
now I know how to do this with date but have a problem doing it with dates and times
Cost Price Vs Quantity Discount
Apr 7, 2008I'm trying to work out how to get a spreadsheet to calculate when it works out cheaper to buy more of something, due to quantity price breaks, than less... e.g. 1-10 are £12.50 each, but 10-20 are £10 each and 20-30 are £8.75 each.. so it's as cheap to buy 10 (10x10=100) as it is 8 (8x12.50=100) and cheaper to but 10 (100) than it is to buy 9 (112.50) than 10... but there are similar price breaks at 20, 50 +
View 9 Replies View RelatedQuantity*price Returns An Error
Jul 15, 2008I am using MS Query to join 3 worksheets together.
I need to calculate one field but it keeps returning an error.
the 2 fields are part of the same table calle "Trades"
The fields are : Quantity and USD Price
USD Price is calculated initially in the worksheet using the current exchange rate.
I can display each fields separately (no problem) but when I try to multply the 2: I get a syntax error.
Things i have tried:
I tried to multiply Quantity by 2 and it works. when I try to multiply USD Price by 2 I get the syntax error.
I also tried to copy the values (not the formula) into a new column called USD Price2 and:
- Quantity*USD Price2 : syntax error
- USD Price2*2: Syntax error
Counting Quantity Of Numbers, With Conditions
May 5, 2006in a2:a277 i have numbers 1-10, randomly. in m2:m277 i have numbers 0-4, also randomly. i want to count how many of the 1s in column a have a corresponding 0, how many have a 1, how many have a 2, etc. if you just give me one formula i can manipulate it for all 40 outputs i need,
View 9 Replies View RelatedSum Quantity And Price Across Sheet Based On Items
Jun 18, 2013I have an issue when I use the sumproduct formula, it only sum the quantity and price orderly which is not matched
I have attache the file for example : Sale monthly.xlsx
Sum Quantity And Show Total For Each Site In A Table?
Aug 15, 2014I've attached a sample workbook. Data Table 2 on the Data Tables sheet is what I'm after. Basically I have a formula that sums up quantity 1 (Q1) and quantity 2 (Q2) for all widgets at a site name and month. So for example, go down through column site name and column month and add up all of Q1 for site1 in the month of Jun 2014 (all widgets). This works but it shows duplicate totals which makes sense as there are mutliple widget types at each site. I think the example workbook explains it better. Note the Pivot Table sheet is an example of what I'm showing currently and what I'd like to show. While I understand that depending on how I set up the Pivot Table I can get similar results using either Table 1 or Table 2 but the other thing is that my users like to do some filtering of the data sheet itself without regard to the Pivot Tables.
sum quantity and show total for each site.xlsx