I 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 RepliesI'm trying to make a worksheet to track inventory, not for sales but for tracking how many supplies are on hand in storage rooms, closets, etc. The worksheet will be used by as many as 20 different users probably on a network.
Because it will be used by many people, I'm trying to see if there is a way that the worksheet can be updated after every save. In particular, the amount that are "on hand".
For example, say there are 10 of a particular item on hand to start. Joe takes 2 and goes to record it on the worksheet. He inputs the 2 he takes, which now show that there are 8 on hand remaining. He saves the worksheet. Then, Jerry comes and takes 2 and goes to record it on the worksheet.
I can't figure out how, or if it's possible, to have the worksheet show Jerry that there are 8 on hand to start, not 10. So that when he takes his 2, it should show that there are now 6 on hand remaining. I've attached an example worksheet.
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 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 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 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 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 RelatedIf I wanted to use excel to track inventory in/out, is there any way I can do the following?
I have a barcode scanner and I wanted to be able to quickly add and subtract quantities.
So, I want to create three fields at the top of list, one with the function to Add 1 to quantity, one to Subtract 1, and one simply to find (well I guess I can use excels find feature, but that would be an extra step)
When I scan a barcode into the "Add 1 to quantity" field (which only appears at the top of the page), excel will automatically add 1 to the corresponding field next to the item that has that UPC number. The same for Subtracting 1.
We have 100+ clients. Each client has inventory in our warehouse. Every day we write down how many items went out to our client on a piece of paper and subtract that number to get a balance inventory. When we run low, we order more on a new FO number. What is the best approach for tracking this? I was thinking one way is to put our clients in a drop box at the top of the page and then the data will appear at the bottom, however I can't get that to work. I am trying filters to see, but I am unable to subtract the balance automatically from the last FO# and from the correct area.
Is there a way to choose a client and product at the top of the page and have the tracking below?
On one sheet, amongst other information, I have a record of how many shirts, hats, and jackets(1 or 2 or 3), each rep has, along with what sizes they are (S, M, L, 1, 2, 3, 4, 5). There are two columns for each. On the other sheet I want to track our inventory. I came up with this to try to count small shirts
{=IF(OR(Hierarchy!$AB$3:$AB$260="S"),COUNT(Hierarchy!$AA$3:$AA$260),0)+IF(OR(Hierarchy!$AM$3:$AM$260 ="S"),COUNT(Hierarchy!$AL$3:$AL$260),0)}
But instead it counts all shirts. how I could modify it to count within the paramiters?
1/A
B
C
D
E
F
G
H
I
[Code]...
I am trying to calculate the inventory coverage. In C3, I have the inventory amount as of january-end, which is 47,6 M. I want to know how many days of inventory I am carrying, according to the later months cogs figures. In this example, I calculated it as 110 days of inventory. is there a single formula I can use, to calculate the inventory coverage for january, and copy it accross the all columns?
I have a workbook that contains several worksheets. The 2 important sheets are Imports and Sales.
In reading the MS KB, the help screens and this forum, I found a way to create a pivot table from 2 sheets using Data>Pivot Table>Multiple Consolidation Ranges. While that PT will provide a basic inventory and profit picture, it only allows 4 selects.
When these products are imported there are several SKU's. What gums up the works is there are several vendors for the same SKU's. Each vendor has a different price. The selling price is constant so the profit made on each particular item varies.
I would like to be able to keep track of inventory by vendor as well as profit by vendor.
Using the Consolidation PT does not seem to work.
I also tried making one sheet to hold all the data. i.e. data showing imports and sales. However, each entry is driven by the date. So while item A was received on 1/1/07, it was not sold until 1/2/07. The necessary columns for imports are filled in while the columns for sales remain blank until it is their row entry indicating a particular sale. At which point all the columns regarding Imports are blank. I swear, looking at the data sheet with all those blanks (actually0's) is like looking at a mouth full of broken teeth. The resulting PT is also not attractive.
I can not imagine I am the first person to have this problem. How do others handle figuring inventory and profits when you have two sheets. Are Pivot Tables the wrong solution? What would be better?
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?
I am trying to calculate the number of days that items are were held in inventory as of a given date. See below for some sample data.
'As Of' - the date in question
# Holdings - Using sumproduct to find items bought on or after the date AND sold after the date
Held Days - ? Total number of days that the six items were sitting in inventory as of the date.
As Of #ItemsHeld Days
5/21/98 6
ItemBoughtSoldTotal Inventory Days
19/3/919/3/961827
21/2/921/2/971827
31/2/921/2/971827
43/2/928/21/951267
55/1/925/1/971826
65/1/925/1/971826
I have a total inventoried product dollar amount in a cell. I have 4 other cells with different dollar amounts in each for product I inventory out! In the seperate balance column, as I use a letter ...
View 7 Replies View RelatedI am trying to reconcile inventory that I have with one of my vendors.
In column A and B, I have my file number and name. In columns C and D, I have my vendors file number and name. Names are last name, first name. Is there a way excel can scan the worksheet and let me know by searching the names, what names are appearing in my inventory and not my vendors and vise versa.
I need a formula that will help determine what "bucket" (based on today's date & due date) a date would fall under. I have 5 buckets:
RRC
0-14 Days
15-22 Days
23-30 Days
31+ Days
I am using the following formula:
=IF(D2="","N/A",IF(TODAY()-D2-26,TODAY()-D2-12,TODAY()-D2-5,TODAY()-D20,"31 + days"))))))
This formula works fine, however the next day I run the report the formula has to be slightly modified to place everything in the correct aging "bucket". Is there a formula that would alleviate the need to update the formula everytime I would like to check the inventory?
The dates are actually based on a weekly (Sunday through Saturday) schedule. So if the Due date is Prior to today's date it would fall into the 31+ category. If due up to 14 days (including today) it would fall under the 0-14 Day "bucket". And so on...
I have a simple inventory spreadsheet that is doing everything I need minus one function. I have not been able to figure out how to make this work yet, maybe I can get some help. We remodel apartment complexes. I purchase material and hand it out to the contractors. I am using one page to track all movement of the items, but I have another page that I want to do that math and tell me what I should have on hand. Example: (On sheet1 "movement") I hand out 20 outlets. On my inventory on hand sheet I have a line that says "Outlet", the current quantity is 40. I have a drop down list so that on the Movement page I select "Outlet" in column A and in Column B I put in the quantity. I want to keep a running total on my IOHand page of everything, so that it would automatically subtract the 20 I just handed out and leave my IOH as 20. I have about 100 items on my IOH page. I assume I need a formula in the quantity cell of my IOH page that will search the Movement page and look for a match to what is in column A and Sum all of my "outlets" or "sinks" into the right cell.
View 7 Replies View RelatedI have a worksheet containing data for a product my company manufactures. I want to make a userform with 1 combobox and 4 textboxes for first in first out management of inventory. From the columns in the worksheet the combobox = "Product", textbox1= "Container", textbox2= "Production Date", textbox3= "Warehouse Location", and finally textbox4= "Sheduled Ship Date".
I need it to work by the user selecting a particular product in the Combobox, based on that the first 3 textboxes are populated based on the oldest production date for that particualr "Product". The user can then input into the 4th textbox "Schedule Ship Date" the appropriate date, then hit a command button to update the spreadsheet with the "Scheduled Shipping Date" ...
I am having a very difficult time determining how to get started with this problem in VBA. I am not sure how to generate the failures, or start the array. I have been racking my brain for quite some time. Company B makes a product that has two component parts. The results of reliability testing for each component shows that the components fail according to an exponential distribution with the following mean time between failure (mtbf).
ComponentMTBF(months)
115
224
Below are Company B's sales records for the past 36 months.
MonthSalesMonthSalesMonthSales...............................
Ive got an inventory of about 2000+ items as well as items that can be broken down into more specific groups such as colors, designs and sizes. What i want to know is how to get the correlation, thats excel function CORREL(), of each item for the time weve had it. The data I can provide is the monthly quanitity sold of the item.
For example I have item A.
Sold Month
5 1
7 2
4 3
5 4
and so on. Easy enough to do. My only problem is I would have to manually do that 2000+ times for my entire inventory to figure that out. What I wants is a command that can verify my item code, which is simple enough as I can convert the item code to something as simple as a string or leave it as is. Not every item has been in the system an equally long time though. If that were the case i could just copy it with blank cells to fill the space and copy and paste all the way down. So I need to find a way to get an IF statement to correlate the corresponding cells IF the item number matches the target cell. What I have right now is something to this extent.
=IF('2'!$A:$A,A1,CORREL('2'!$B:$B,'2'!$E:$E))
A friend of mines wife has decided to get into MaryKay and he is wanting to create a spread sheet to keep track of the product she has.
He wants to be able to enter the product in and at the end of a show subtract whatever product she sold with a sheet showing what she has in stock.