Inventory Tracking Spreadsheet

Jun 28, 2007

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.

ADVERTISEMENT

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 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 Trend Tracking

May 31, 2007

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

View 4 Replies View Related

Excel Template For Tracking Inventory?

Jan 2, 2012

Quick rundown, I have a sales background, so other parts (accounting, inventory, etc) are what I want to improve so Im not running around when an order comes in, where is it, do I have it in stock.

I know microsoft has some templates, any worth downloading and starting with, then move the data or add functions to an inventory and accounting template to start, any other templates recommended for an online business.

View 3 Replies View Related

Tracking Age Of Inventory In Warehouse FIFO Accounting

Mar 8, 2014

I am trying to create a worksheet that tracks the age o f the inventory in my warehouse. I am charged each week at an increasing rate and want to be able to track what these charges will be. I would like to be able to sum up the data below in a pivot table that shows how many units are falling into each age group, this would add up to a max o f 10 weeks o f increasing charges.

3/1/14 received 1000 units
3/6/14 ship 600 units
3/8/14 charge for 1 week at 400 units
3/8/14 receive 500 units (900 pieces on units)
3/13/14 ship 300 units
3/15/14 charged for 100 units at 2 weeks and 500 units at 1 week

This needs to be able to assume this is going on for 10 different items, I would like to be able to track the items independently and in bulk.

View 1 Replies View Related

FIFO Inventory Method Balance Tracking

Jul 7, 2009

I've been trying to come up with either a formula or a macro (I'm a novice in VBA so it would probably take me forever to figure it out) to track the FIFO balance (First In-First Out) of items at any given point in time. I have attached a sample spreadsheet with the example and all the notes that might help figuring this out.

I want to stress that I'm not trying to calculate any cumulative balance or any FIFO pricing whatsoever; just separate tracking of the purchase balances at any point in time after varous sales using the FIFO balance.

View 9 Replies View Related

Tracking Who Opens Spreadsheet

Dec 19, 2007

I have a spreadsheet that can't be password protected but this doesn't mean just anyone should be accessing it. Is there a way to record who opens it and when? Ideally I could place some code into the spreadsheet that recorded on a sheet who has opened it and when. This will detract certain nosy people from accessing it!

View 9 Replies View Related

Call And Sales Tracking Spreadsheet

Apr 5, 2011

I am in sales and we currently have a excel spreadsheet with call tracking and a sales tracking on it. I would like something a little more inviting. We have quotas that we have to meet. 100 calls a month, 20 quotes to put out, $11k is sales.

I was thinking maybe something that was broke down by week for calls. The sales and quotes, have a graph associated with them. When we type in the dollar amount it will raise the graph that much. I think this will make it easier to manage. I have attached what our call tracking looks like now.

View 3 Replies View Related

Tracking Spreadsheet - Pop Up Window Entry

Oct 28, 2013

I am currently producing a work number tracking spreadsheet for the people I work with and wanted to know if there was a way to generate a pop up window that created fields to enter the data for the job being inputted.

I currently have it just a simple macro (below) that enters a new line, adds a sequential number and adds both the date entered, as well as an expected finishing date. It does what I want it to do, however I am looking for it to be fool-proof as the last system was broken as people didn't fill it in fully.

Code:
Sub New_Line()
Range("A4").EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Range("A4").Value = Range("A5").Value + 1
Range("C4").Value = Now()
Range("D4").Value = DateSerial(Year(Now()), Month(Now()) + 1, Day(Now() - 10))
End Sub

On the previous system it used a worksheet that did similar, with dropdown menus that auto-filled the sheet as well as inputted into the spreadsheet. That was produced by an old employee that isn't working on it any more.

View 7 Replies View Related

Formulas For Baseball Tracking Spreadsheet

Jul 3, 2007

My spread sheet reads column A teamname, B games played, C wins, D Losses, E Ties, F winning % and G GAMES BACK

I'm looking for a formula to determine games back based on wins, losses, ties and also based on the number of games played.

example, team 1 played 9 games and is 8-0-1. Team 2 has played 9 games and is 8-1-0. Team 2 is would be a 1/2 game back. Team three is 8-0-0 also a 1/2 game behind team 1 but tied with team 2.

So in column G it would read team1 "-", Team 2 ".5" and team 3 ".5"

View 9 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

Count Formula- Spreadsheet That Is Being Used For Tracking Work Completed Each Day In A Week

Apr 9, 2009

I have a spreadsheet that is being used for tracking work completed each day in a week. Each day has 5 columns and 10 rows of data to potentially be entered, some of the data is text and some is numerical. Can anyone help me come up with a formula that will count the text and the numerical entries for each day? I tried to use a nested if statement but it exceeded the number of nestings available. The range of cells for the first group is B6:F16.

As a side question, will count work for a range of data or only one column or row at a time?

View 9 Replies View Related

Filtering For Summary: Produce A Spreadsheet For Tracking Pupil's Progress Through A Year At School

Oct 6, 2008

I'm trying to produce a spreadsheet for tracking pupil's progress through a year at school (Targets/Predictions etc) but am having some problems with creating a summary of data gathered for each pupil. I have attached the file for your perusal; On the data entry sheet staff will fill in the appropriate data, and on the summary sheet I am looking to generate summaries for each pupil (I have set up how I want it to look). This may sound easy, (and probably is); the issue is that there are around 30 subjects in total, but pupils will only have be doing 5 of them, I need the summary sheet to show the information for subjects they are taking only (It should come up with the subject name under the headings subject1/2 etc and the appropriate grades to go with them, missing the blanks out. Obviously I could do this by cutting and pasting for each pupil...But there will be almost 2000 pupils in the list!!!

View 4 Replies View Related

Formula For Advanced Spreadsheet To Determine Inventory Count?

Mar 23, 2014

We have a spreadsheet that pulls data from various other spreadsheets that contain information on current year data, budget year data, and last year data. We need to pull from the current year spreadsheet information each month on the inventory count on hand.

We need one formula in one cell of the main spreadsheet that will know to pull from the appropriate month column of the current year spreadsheet based on the particular month we are running the report for.

In other words, we are not wanting to need to change the formula each month, but to write it such that it will know by our indicating in the main spreadsheet which month it is (which is always indicated in Cell B14).

In the main spreadsheet, the cell containing spreadsheet information on where to find the current year data is $D$2.

View 8 Replies View Related

Reorganize Spreadsheet To Comply With Vendors Inventory System

Oct 14, 2009

Basically We have a column with our part numbers and a column with the vehicle applications which contains the years, make, model, and engine.

Most of our part numbers have multiple applications that they work for.

The vendors system only uploads unique part numbers, so I need to find an easy way, if posable, to combine all the applications that use the same part number in one cell next to it's specific part number.

I don't know if I should be messing with formulas, pivot tables, VBA...?

View 7 Replies View Related

Inventory Spreadsheet - Transfering Count To Master Broken?

Nov 19, 2009

I went to add the count to the inventory, it keeps returning an error where the date is pasted from one sheet to the other. I had originally tried to reference the value of the date directly, but because it was being passed to a merged cell, Excel didn't like it too much, so I had it copy and paste it. That worked at first, now it suddenly doesn't.

View 2 Replies View Related

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

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

Tracking Changes To A Log Across Workbook

May 28, 2013

I am looking to track changes on a "Log" sheet throughout an entire workbook. The following code (found here and given below) will give me:

The cell . The date/time it was changed . What the new content in that cell is

The two points I would like it to include, in order of importance, is: The name of the worksheet that cell exists in. The user that made that change*Bonus points* a hyperlink that'll bring me to the worksheet that was edited.

VB:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim r As Long, OutSht As Worksheet
Application.EnableEvents = False
Set OutSht = Sheets("Log")

[Code] ......

View 7 Replies View Related

Tracking Changes In Workbook

Jun 28, 2006

I'd like to make a vba macro in excel which will be just like "Track changes" feature. I'd like to track changed cells and when and who did it. (Date, User, Cell, Last value, New value). It is important to make a report in separate worksheet and automatically fill this worksheet when the user save changes.

View 5 Replies View Related

DDE Change Tracking

Dec 2, 2006

Aaron Blood has created an excellent DDE Change Tracking.

The file is in the download area of [url]

The macro, in the xls file, archive the data from top (a1) to the End of the worksheet.

I want invert the way to file/archive the data… copying all the cells already filled, a cell down and filing the top ( A1...) with the last DDE value (In the TracK sheets).

How can I do that?

The primary code is:

Sub Change_Tracker(Watch As String, TrackOn As String)

Dim NextCell As Range, r As Double, c As Double

With Worksheets(TrackOn)
r = .Rows.Count
Set NextCell = .Cells(r, 1).End(xlUp).Offset(1, 0)
End With

View 4 Replies View Related

Reservation Tracking

Jan 3, 2007

I manage a small motel with 6 rooms and I'm trying to write a simple program to track my customer reservations including their details. All the commercial software is too elaborate and way out of my price range. I'm slowly learning excel but this has proved beyond my capabilities.

View 9 Replies View Related

Tracking Paid Transactions

Mar 25, 2014

I want to create a spreadsheet that I can export my transactions from my credit card onto -- is there a way to make it so that the transactions that haven't been covered by my most recent payment(s) are red, while the ones that are paid are green without manually going through & doing it? I know there's the IF, TRUE, FALSE formulas, but I'm confused on how to use them.

Basically, if I spend $1,000 between 5 transactions and make a $400 payment, I want the oldest transactions totalling up to $400 to turn green, while the remaining are stay red until a new payment is posted.

View 1 Replies View Related

Change Tracking Automatically

Aug 21, 2007

I have a spreadsheet i would like to have changes tracked to automatically. I know it's only a matter of clicking a few buttons to get this to happen, but i was wondering if there is a way to have excel do this by default when i open the file. I would also like to have the changes highlighted automatically.

View 9 Replies View Related

Tracking Updates And History

Feb 5, 2014

I have an actions tracker worksheet.

I want to type status update for an action - then when I press enter it should automatically as soon as I press enter:

- cut the text I typed from the cell leaving it blank
- move to the target cell (its in the same row) and "add" that text string (with a date stamp added) to the text already in the target cell.

Every day I type an update against the action in the first cell - and I press enter - it should copy that text into the target cell.

That way I have a complete history of updates against the action with date stamps.

Sounds pretty simple but I don't know where to start.

Example of a simple worksheet attached : Tracker.xls

View 2 Replies View Related

Tracking Open Trades

Feb 20, 2009

I want column J to reflect the total amount of all trades which were open at the time the trade on the row is closed.

(the data is sorted according to closed trade times).

So while the trade on row 4 is the first trade to close, there were other trades also open prior to the closing. I need to show the total amount in column E of all trades which were open during the time window that the trade on row 4 was open.

View 4 Replies View Related

Tracking My Employee's History Of Changes.

Apr 1, 2009

I am using excel as point of sale book (to record customer name, service, and total price etc.) at our hair salon. We have employees that may be there to manage alone from time to time, and enter clients into excel.
Our worry is straight forward, and involves them erasing what they wrote. I am confident that the actual service and price is entered at the beginning, but want to track their changes to their own entries.

The "track changes" would work if it "tracked changes" after entry. But it seems to track the last change from saving. For example....the employee enters $40.25 presses enter--after she knows she can get away with a change, she may erase it altogether or change it to say $16.75.

View 9 Replies View Related

Tracking Date From An Excel

Aug 13, 2009

I own a restaurant and have a inventory list. I track the price of a specific item in column E, and want to record the date i modify column E in column D. So, I change E5, I want D5 to say the date.

View 9 Replies View Related

Tracking Changes Without Sharing The Workbook.

Jun 21, 2006

I have a workbook which has a database and a pivot table. I wish to track changes without sharing the work book.

Is that possible?

Also how can I keep track of the users accessed to the workbook ?

View 3 Replies View Related

Reverse Tracking Of Data

Nov 9, 2006

if data values are not input and are result of formula or macro can traverse back to see which macro is used to fill the data.

View 3 Replies View Related

Code For Tracking Location

Mar 6, 2007

locate code for highlighting the row and column I am in at any given time in a spreadsheet. I work with some pretty large sheets and find it hard sometimes to know if I am on the right line or not.

View 4 Replies View Related

Tracking Of Credits (Hours) With Expiration

Mar 31, 2014

I work in a HR department and I'm trying to create a spreadsheet to track the amount of extra-duty hours each worker has. For every extra hour they work today, they can use this hour next time.

Screen Shot 2014-03-31 at 9.25.22 pm.png

This is my current spreadsheetCredit hours means hours added to their 'account'.Debit means hours taken out.Hours will expire in X days if not utilised.Right now, each worker has their own spreadsheet in the same workbook.I have about 25 workers.Is this the best way to manage this?How can I create a 'bank account' system to track their hours?

hourstracking.xlsx

View 1 Replies View Related

Tracking Date And Time Overlaps

Jun 18, 2008

I'm trying to figure out a formula that would track a student's class schedule to see if there is a date/time conflict. I'm uploading an example of a ficticious student named Amanda for you to review.

According to the example, I should be prompted that there's a conflict between Amanda's first two classes because they both meet on Monday (M) and Wednesday (W) and the time during each of those classes overlaps.

This will need to be checked per student, based on their ID in column A.

View 10 Replies View Related

Tracking Weight Loss In Excel

Aug 2, 2008

How do I track my total weight loss in excel? Here is an example of what I am trying to do.

8/1/2008 228lbs
8/2/2008 225lbs
8/3/2008 223lbs

Total 5lbs

I would like use a formula that would subtract my absolute weight loss total day by day in August. Basically, I want the total "cell" to equate to my total weight loss in August. For Example after 8/3/08 the "total cell" should equate to 5 lbs. What formula do I use to do this?

View 6 Replies View Related

Job Hour Tracking With Pivot Tables

May 19, 2009

I have an Excel timesheet that the supervisor loads the time (in and out) for all the shop employees. These employees may work up to six different jobs in one day at various points throughout the day. I have no problem with calcs and formats on the daily time entry tab (sheet1). What I need is a way to track each job using a running total so that as the job is in progress we can see how we are doing against estimated hours. I thought perhaps I should at least make a weekly time summary pivot table thinking that it would make it easier to calculate, but it didn't help with running totals. The summary included (5) copies of the same daily entry tab (sheet1) just given a different name, in this case ..a simple date change and save the sheet with the date used. OK, here's the problem. How do I create a running total that would track each job? I forgot to mention earlier that each daily time entry sheet is saved and named by the date for which it represents. The weekly time summary pivot table works fine....but it only gives me the grand total of hours worked for each day...not a running total summary. I can view the pivot tables and go to the calcualtor and add the hours up per job, but that's defeating the purpose. Anyway, I hope this is clear enough to at least get some responses. Then, maybe by then I'll have a better understanding how to ask the right questions.

View 9 Replies View Related

Call Tracking Sheet - Electronic

Nov 3, 2009

I am trying to turn this call tracking sheet into an electronic one. Is is possible to have check boxes in the empty cells that will allow someone to just click them as they go along and it would create a total at the bottom to avoid countless hours of counting these sheets. Or even turn "Trans CM" into a button that you can click and it would keep track of how many times that specific button was clicked.

View 3 Replies View Related

Automatically Tracking How Much Material Is Left

Dec 14, 2013

basically I want to be able to keep track of how much vinyl material I have left after each order.

The process would be - When I order a roll of vinyl material I would input the colour ordered and cm ordered by selecting from drop down lists in the 'Vinyl Tracker' sheet. When a customer makes an order, I would select an item, size and colour from drop down lists in the 'Orders' sheet. Depending on what size is selected in the 'Orders' sheet, I would then like Excel to automatically update column 'Cm Remaining' in the 'Vinyl Tracker' sheet, however using the smaller number in the relative size column from 'Item Sizes' sheet.

E.g. If we take the first order:

World Map
Medium
Black - (M)

This would then refer to cell M5 in the 'Item Sizes' sheet (as 43.35 is less than 90).

I would then like the number which is retrieved to be taken away from the relevant cell in the 'Cm Ordered' column, depending on what colour was chosen in the 'Orders' sheet.

To make matters a bit more complex, obviously when any of the numbers in the 'Cm Ordered' column in sheet 'Vinyl Tracker' is 0, I will re-order the same vinyl roll and insert it into the sheet as per usual. How can I make it so that any new orders will take away from the latest instance of the same coloured vinyl?

View 2 Replies View Related

Tracking Elapsed Time With No Date

Mar 4, 2007

I have an assignment to audit the time it takes convert a printing press from one job to the next. I've been using a watch to take the readings in hours, minutes, and seconds.

I recorded them in my spreadsheet, as h:mm:ss, but it showed a date also. I think I finally got that resolved, but now, when I try to subtract one time from another time, I get an incorrect answer. I think I need to convert all of these times into decimals, as in 13.23.44, as oppossed to 13:23:44.

One post on page 1 of my search said to multiple A1*24, but I don't fully understand. I tried that, but 11:30 became 11.50?

I need to extract the set up time, or job to job changover time in a decimal format if possible. There are other tasks being done with those numbers later in the spreadsheet that are more conducive to decimals, than the time format.......

View 3 Replies View Related

ADVERTISEMENT