I have an inventory list (non-sequential) and I want to pull only the items with id codes that are with in 4 different ranges.
I would like to populate column A with the results, search paramiters are in Q3 and S3.
I can get it to work on a small scale but my inventory list is 10143 rows long.
I have a set of data like below, I am trying to get a formula to look at a range i.e. May column and then select the highest number in that range but rather than returning the number returning the team name, is that possible? Not sure where to start...
Jan
Feb
Mar
Apr
May
Jun
Team 1
25087
23029
32991
21319
25271
25136.8
[Code] .........
I have a spreadsheet listing all my vehicles and their respective oil filter part number. Now, some vehicles share the same oil filter and I am trying to find a way to return the vehicle description when using the filter # as a value. In my example below, I am looking to fill the third column with the Vehicle that also shares the same oil filter number. i.e. in the Dodge row, toyota would appear in the third column and vice versa in the Toyota row.
Vehicle
Oil Filter #
Also used with
Dodge
51515
Honda
54565
Toyota
51515
I am not well versed in excel lingo which is probably why I have a hard time coming up with a method that works.
I'm using excel as an inventory database but need to know how to change the number format according to what the user is keeping track of. For example drums, bags , cylinders ect...
How can i change the number format to display the unit of measurement added by user?
How do I print multiple inventory labels based on our inventory levels?
For example:
Item No 1000
Descr Window
Customer Taylor
PO 9001
Quantity 10
Item No 1010
Descr Door
Customer Jones
PO 9011
Quantity 35
I want to print 10 labels with the info from Item No 1000 and 35 labels from Item No 1010????
I am looking to calculate how many days worth of inventory I'm currently holding (in stock and on order from supplier) based on my sales over the past 30 days.
I've seen a number of formulas around... and honestly am not sure I'm on the right track.
On the attached I have used the following:
(Stock on Hand + Stock on Order) * 30 / ( Units of the item sold in the past 30 days)
I am having some difficulty with a macro. I have a column that contains a series of inventory numbers. I need to break it up into three parts and then have each part put into a new column. Here is an example of the data:
Column B = list of inv no.’s such as “ABC123.45” I need to divide it into three parts and put each part into a new column … so a new column X would get the “ABC” part, a new column Y would get the “123” section, and a new column Z would get the “45” ** ( I don’t need the decimal between 123 and 45) .
I'm trying to filter data into a cell that meets certain criteria...
I would like to count the number of times a sku is found in each region in each month... daily inventory counts are recorded.. the date is recorded as MM/DD/YYYY...
is sumproduct my solution? I'm getting errors, specifically #NAME?
=sumproduct((sheet1!L:L=SKU)*(sheet1!M:M=Region)*(sheet1!C:C>=1/1/2009)+(sheet1!C:C
I had been trying really hard to get some solution on COGS valuation & inventory valuation on FIFO basis. my daily transactions are typical sales & purchases.
Attached is the inventory in/out movement from Quick Books. in the column "Num" type bill is the entry from purchase bill whihc always has a reference as P/O####. This is how i will capture the landed cost against a PO., another type "Inv Adj" is inter warehouse transfer. Name is cusotmer, Inventory is my item number. in some cases it is like "2000", in some cases it is 10000:10121, and in some cases it is 10000:10200:10201 that is why they fall in different columns when i export them.
What I would lilke to do: 1) Run a report by month, by customer showing cost of goods sold on FIFO basis, I can capture sales amount by running another report.
I am not an excel whiz, so I will need layman's terms if possible. I may have found a solution to my problem under another thread, but I couldn't understand it. I have only briefly worked with macros, a very long time ago. Here goes...
I have an inventory list that has not been updated for sales of the inventory, only for purchases. I have sales lists, generally by month, in separate spreadsheets. I am looking for a simple, efficient way of either eliminating or at least matching up the data in the sales list to the inventory list in order to remove sold items from inventory (on paper). I am hoping the result will be a fairly accurate inventory list, and related value, so that a physical inventory count won't be necessary at this time. Obviously I could copy all of the sales info into one spreadsheet, sort by inventory number, then manually delete all sold items from the inventory listing. I am REALLY hoping there is a better option.
I need to send an email to each of the suppliers their present inventories using Outlook. If inventory is "0", set high priority flag. Example:
Supplier Item Qty Email address
A 123 10 Johndrew@link.net
A 456 2 Johndrew@link.net
B B10 20 Reliable.xom
B B11 0 Reliable.xom
C C22 9 Cold@Cola.com
I need a formula which would take the last number that appears in a column and place it in A1.
The column is D10 through to D44 and the last number can appear in any one of those cells.
How to extract only the number out from the description?
the number can be in left, middle, right..
Description Result
1930 profit 5301000 Meal allowance1930 5301000
expenses 1930 meal the 2701000001930 270100000
I have a workbook. The second tab is inventory numbers and the first tab has my inventory items. I scan in the inventory number (unique) and it adds it to my inventory sheet. As I use inventory I scan the barcode and it inputs the serial number into my used inventory tab. What I need to do it when I scan the serial number for used inventory into the used inventory sheet to have it go to the inventory sheet and delete the line for that serial number.
View 9 Replies View RelatedI have over 7000 items with a unique 6 digit item number each. They are separated buy category numbers. So category 5 may have 30 item numbers in it. column A has 555555555666666666777777777. Column B has the item numbers. I have about 200 categories.
In the workbook I have separate sheets for each category.
I need to pull all the item numbers from category 5 to sheet tab 5, all cat 6 numbers to sheet tab 6 and so on.
I do know excel but never used code.
5
562462
5
845294
5
349124
[Code]....
I have a worksheet that is full of names (column A) and dollar amounts (column B). Many of the names are repeats.
How do I pull the largest dollar amount for each unique name in the worksheet?
I need to be able to pull the information into another section of the workbook and place it into order (ie Black 1,2,3,4) and then drop down to the next row and place Blue 1,2,3,4 etc. I am using a drop down menu to select the Course, and want the times to then populate to the right, each in its own cell.
Have tried the loop code available on a few different topics here however has not quite worked.
Excel 2007
ABCDE12145101843222121028543291410388563015104796731501058178325210
685894953107839104354108841011445510985111349661101215516710013135668
981417576999151858708916195971801720607291182161738219236274902024637
59121276476872228657786232425Sheet1
Designated Cell = 7
1 10 .034
15 25 .072
35 45 .089
Output Cell = ???
I am trying to find a formula for the output cell. Suppose the designated cell = 7 due to formulas above it. So that number is always given but can change. I then need a formula for the ouput cell that says if the designated cell is greater than or equal to 1, and less than 10, then the output cell should equal .034. So suppose the designated cell = 18.2. Then I would want the output cell to be .072 since it is greater than or equal to 15, and less than 25. The template I am using has 23 rows of numbers like above. I didn't know how to do it for 23 rows though. If/Then doesn't work because it is 23 statements long (well over the seven limit) and the vlookup only works when the cell is equal to a number and not a range.
I would like to count the number of occurence of a user given number in a range through VBA code. Have attached a sample with this.
View 2 Replies View RelatedFirstly i am running Excel 2003 (at work)
Now my problem is that i have been asked to make a spreadsheet about Printer and Fax Cartridges (Zzzzz) but i do not know how to do the sum.
this is how my Spreadsheet looks like this (cartidge amount etc all on top line of spreadsheet)
(Hp45 etc all down left hand side) ...
I AM TRYING TO SUM OF EACH DAILY INVENTORY ITEM. PREVIOUSLY I USED FORMULA SUGGESTED FROM TEETHLESSMAMA (=SUMPRODUCT(--($A$5:$J$13=A19),$B$5:$K$13)).
BUT THIS FORMULA NOT WORK FOR NEW FORMAT OF INVENTROY DATA. I tried to make some change in it to get the result, which is not working well.
I am doing inventory right now and need help sorting numbers that I have. Ex:
I have numbers like
10
1001
101
AB1394-10
AB2948-MPG
XD89-MPG KIT
F64 KIT
GSW294-HD10
I need something that will help me sort it by the numbers only (numerically by the first part only). I want the above numbers to look like the following after sorting:
10
F64 KIT
XD89-MPG KIT
101
GSW294-HD10
1001
AB1394-10
AB2948-MPG
Basically, I'm trying to get the computer to sort the numbers numerically by disregarding any letter before the number.
I have a worksheet that has multiple units listed on it. Each unit has a materials used list with quantities. I want to reference this column to another sheet and get a total materials needed list and also reference it to my inventory and subtract that number from my materials on hand. how can I do this?
View 6 Replies View RelatedI am trying to figure out a formula for doing my inventory. I have items with control numbers on them and I would like to determine how many items there are. Example Starting #12335A Ending #12445A, if you subtract 12445A from 12345A you get 10, but in the inventory you need to count the ending number as it still remains in your count, so the answer is actually 11. I have been using for example =Sum(A8,-A9) +1 to get the correct number. However, if I have additional lines on my report that do not have inventory in them, they will show an incorrect total of 1 because of the +1 in the formula. I am trying to figure out how to do this calculation without having to delete and add the formula each time inventory is added. I have people that will not go near the formulas, so I need it to total with out having to be changed.
View 8 Replies View RelatedI am trying to put together an inventory worksheet and I have a rec'd column and a usage column, followed by a total column. I want to be able to havea formula in the total column at all times, so whoever updates the inventory sheet just needs to put rec'd and used. What I am trying to do though, is have the total row for a particular month blank, unless someone enters a rec'd amount or a used amount.
View 9 Replies View RelatedSo far I was able to find solutions to all of my small problems with excel just by searching here but with this one I need help and ideas how can it be done differently.
We need to track inventory from when its taken, when it was installed and we need to know every month when inventory is counted how much do we have on hand in our truck....
I'm trying to set up an inventory spreadsheet for my work, but i'm running into a snag.
What I want to do is have three columns that interact with eachother.
1. Units in stock
2. Reorder Level
3. Reorder Tolerance
4. Status
What I want, is to have the status column react in one of three ways. Either "In Stock", "Reorder Needed" and "Out of Stock".
So if units in stock is at 110, with a reorder level of 100, and a tolerance of 10%, I want the Status to show "Reorder Needed" with a red cell color. But if stock is at 111 or more, I want it to be shown as "In Stock". Zero should be "Out of Stock".
I have inventory on an excel sheet with over 1500+ items. There are number of columns including: product id, product code, description, price, headline, path etc. I am uploading this to my online store I am creating. And, I have a lot of duplicate items so I can place them in different sections of the store by editing their path. However, these duplicate items in the excel sheet only have the product id, and not the description, price, and all the other cells. Thus, I need a forumla that will find matching product id's and fill in blank cells with their description, price, etc. so it will match its partner completely .......
View 14 Replies View RelatedI having trouble to sort out and summarize inventroy products for each week. I want to sumarize all weekly inventory in summarize table. Please see attached file. Pls keep the formate as i shown in attached spreasheet.
View 9 Replies View Related