Eliminating Sold Inventory From The Inventory List

Aug 8, 2006

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.

View 6 Replies


ADVERTISEMENT

How To Print Multiple Inventory Labels Based On Inventory Levels

Sep 10, 2012

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????

View 8 Replies View Related

FIFO Inventory- COGS & Inventory Valuation

Aug 18, 2009

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.

View 11 Replies View Related

Second Tab Is Inventory Numbers And The First Tab Has My Inventory Items

Feb 27, 2007

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 Related

Attempting To Create Inventory List....

Mar 4, 2008

(Excel 2007)

What I'm trying to create is a way to populate a quick inventory list with my bar-code scanner.

I've already learned that in order to have excel move to the next row each time a 12-digit SKU number is scanned, I'm going to have to utilize a userform text box.

My problem:

I don't even know how to begin coding the text box to accept a 12digit number, enter it into the next available blank cell (I'd like it to populate vertically) and then ready itself to accept the next available 12digit number.

My desired end-result:

My scanner is attached to a lengthy USB-Extension, so I can roam my store and quickly scan the inventory on display. For that reason, running back to my laptop and striking "Enter" each time makes the whole solution pretty useless.

Once finished, I'm assuming I could use excels count feature to tell me the quantity of the items I have on hand.

View 9 Replies View Related

How To Make Inventory List Using Barcode Scanner

Jan 20, 2012

i am brand new to this forum, and not so fluent in excel for more complicated tasks.

i have been looking for a solution for this online, but seem to be running in circles, so thought i would post here.

I want to make an shipping list using a barcode scanner. i have a master list that contains all of the inventory numbers and the relevant item data in each row. i would like to be able to scan the barcode of the item and have excel automatically look up the row from the master list and put it into the shipping list.

View 5 Replies View Related

Creating Spot Check List From Inventory List

Mar 17, 2014

I am looking for a Macro that would randomly select 20 items from my inventory list for performing spot checks. Column F of Active Coil Log tab is where the data would need to pull from, however I would like all data in the row to go along with it. I've explored the RAND functions, but they don't seem to be the right fit .

View 2 Replies View Related

Parsing List Of Inventory Items Into Names And Numbers

Mar 28, 2014

I've got a long list of inventory items. The naming convention of these items is not totally consistent. Below is a representative sample of all the different kinds of names:

1800Brandacom (CLIENTA) 25.00
1800Brandacom 25.00
brandb (Variable)
BrandC-100.00-PL-ALG
brandd variable

[Code] .....

I need to separate out the name of the brand and the number (or if the item says "variable", use a 1 for the number) into two columns, so that the results would be:

1800Brandacom
1800Brandacom
brandb
BrandC
brandd

[Code] ....

and:

25.00
25.00
1.00
100.00
1.00

[Code] ....

For the numbers, I have used this array formula to search for the first letter in the string, and then search for the first number following the first letter, and then return all numbers up to "." Unfortunately this does not work consistently when there is a number in the middle of the brand name or the item has no number.

[Code] ....

For the names I have used this formula, which fails when the name has a "-" in it.

[Code] .....

Any way to extract the names and numbers which would work consistently across all the items!

View 4 Replies View Related

Variable Dropdown List For Inventory List

May 24, 2014

Im trying to organize a tools inventory list. Its supposed to be sort of like an order sheet for each department. But what I want to do is when I select either CARPENTRY, or any of the other trades from a drop-down list, I'll be able to select from another drop-down list items associated with that trade in particular. So eventually I want the master data hidden, preferably in another sheet. I searched all of Google but it seems a bit complicated to get it done. I've attached the sheet so that you can see what I'm talking about. MOVE IN MASTER LIST.xlsx

View 8 Replies View Related

Subtract From Inventory

Jan 28, 2008

Firstly 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) ...

View 9 Replies View Related

SUM Of Daily Inventory

Feb 9, 2010

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.

View 9 Replies View Related

Inventory Sorting

Jul 3, 2009

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.

View 7 Replies View Related

Inventory Tracking

Feb 12, 2007

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 Related

Inventory Count ..

Mar 7, 2007

I 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 Related

Inventory Worksheet

Apr 17, 2009

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 Replies View Related

Inventory Tracking ...

Jan 29, 2010

So 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....

View 7 Replies View Related

Inventory Spreadsheet Formula

Nov 5, 2009

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".

View 6 Replies View Related

Inventory Sorting & Matching

Nov 30, 2009

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 Related

Summarize Weekly Inventory

Feb 7, 2010

I 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

Track Inventory In / Out Using Excel

Oct 7, 2011

If 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.

View 14 Replies View Related

Setup New Inventory System?

Aug 26, 2013

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?

View 5 Replies View Related

Worksheet To Track Inventory

Apr 12, 2008

I'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.

View 6 Replies View Related

Counting Inventory Within The Paramiters

Oct 17, 2008

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?

View 5 Replies View Related

How To Calculate Inventory Coverage

Apr 11, 2014

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?

View 5 Replies View Related

Inventory And Profit/Loss

Feb 11, 2007

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?

View 11 Replies View Related

Number Format For Inventory

Mar 6, 2007

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?

View 8 Replies View Related

Calculating Inventory Days

Mar 9, 2007

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

View 9 Replies View Related

Dollar Amount Inventory

Sep 3, 2008

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 Related

Comparing To Reconcile Inventory

Nov 11, 2008

I 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.

View 7 Replies View Related

Aging Inventory Buckets

Aug 27, 2009

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...

View 9 Replies View Related







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