Create A Tally Sheet To Keep Track Of My Inventory Of Inserts
Aug 22, 2009
I am trying to make a tally sheet to keep track of my inventory of inserts. I am trying to make it as user friendly as possible as my operators do not have much experience working with computers. I will attach what I have made so far. The only math functions so far are: C4=B15-C15.
Right now the operator has to look at C4 to see current total, type that number into B15, then in C15 type the number of inserts thrown out to show a new current inventory total in C4. Is there a way I can set this up so that all an operator has to do is type in only the number of inserts thrown out in C15 to give current total in C4. Is there a way to make B15 know what is in C4 without the operator having to type it in. If so is there also a way to make C15 the only cell that can be edited.
View 3 Replies
ADVERTISEMENT
Jan 8, 2007
(1) My main tab I have a macro button that inserts a new row and also inserts a new sheet with the rows name that was inputted. But on the main tab in cell A1 that is named, I can not get it to hyperlink to the new tab.
(2) When the button is hit to insert a new row and sheet I copy over previous tabs information and then I want to change two fields A1 and B1, A1 works fine but my B1 I can not get the formula to work correctly. My cell in B1 I need it to say 'DESCRIPTION: ' and then take the value of main tab 'Test Case Summaries' cell D?, it would be the new line and copy it here. See below... rname is the new tab name and lrow is the new row that was added when the macro button is hit.
t = Sheets.Count
Sheets(t).Copy after:=Sheets(t)
ActiveSheet.Name = rname
Sheets(t + 1).Range("a1") = ("Test Nbr " & rname)
Sheets(t + 1).Range("b1").Formula = "DESCRIPTION: " & "!$D$" & lrow
View 9 Replies
View Related
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
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
Feb 17, 2009
I am building an inventory application in 2007. I need to track the movements of the items so I was planning on a worksheet containing the item no, date, location.
Since an item will move around and thus have multiple records, how can I extract only the latest date record for each item and store them in another worksheet ?
So I would have 2 worksheets, a. transactions, and b. current inventory. Or am I trying to reinvent the wheel. I am sure there are a lot of inventory applications already written - how do I find one? So, 2 question!..
View 5 Replies
View Related
Apr 11, 2014
I'm trying to make an order form that is based off of a price list. Basically there will be 200 items or so someone can just enter the quantity they want into a column. I would then like another sheet on the workbook to auto-populate all of the fields available. The thing I'm having trouble with is I don't want the finished form to be as long as the price list with blank rows in-between. I've been reading up to make a macro work for this, but have had no success.
View 3 Replies
View Related
Feb 1, 2009
How do I get some columns in sheet 2 to be a 'Live' copy of some colums in sheet 1? - 'Live' as in they include any changes to sheet 1, i.e. insertion/deletion of rows?...
I have set up a very basic shared workbook for me & four colleagues so we can all see what each others work loads are like and establish who has capacity for new work. We each have a sheet and record the work data in rows which details Name of case, Case ref no., age in days,etc... This then feeds a summary sheet displaying the totals of the relevant info and has pretty charts etc which we can print off for our manager.
As our cases are concluded we simply delete the rows. When we get new cases they are either added as new rows or inserted between existing rows (when similar work/related work). What I would like to do is give each of us a second sheet where we could record more specific detail regards each case. I would like sheet 2 columns A&B (detailing name name & Ref No.) to be the same as Columns A&B Sheet 1 - I have done this with a simple =Sheet1!A1...etc, which is great until any deletions/insertion are made to sheet 1 which then throws out sheet 2. How do I make Columns A&B for sheet 2 'live' copies of Colimns A&B Sheet 1?
View 3 Replies
View Related
Nov 26, 2007
i am currently trying to figure out excel i have to fill this worksheet out daily and then at the end of the month i have to count the total number of items. I was wondering if there was a way to auto talley on a different sheet so that everytime i entered a product name and quantity if on the other sheet it would auto add it in
example:
I ate at mcdonalds (Main sheet)
On 11/22 I had 3 big macs and 4 quarter pounders,
On 11/24 I had 5 Big Macs and 2 quarter pounders
On 11/26 I had 12 Bigmacs and 5 quater pounders
(New Sheet) i want it to look like this:
Product quantity
Big macs (20) <-- this number i want to auto add from the notes made from main sheet)
So on another sheet i want to make a formula where for everytime i type in "big mac" on main sheet that on the other sheet it would auto add or keep a running tally so after i made those 3 entries on the main sheet it would show that i had a total of 20 big macs on the new sheet.
View 9 Replies
View Related
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
Dec 20, 2013
Time. What formula do I use?
6:30AfterBreakfast
7:30AfterBreakfast
8:00AfterBreakfast
8:30AfterBreakfast
9:00AfterBreakfast
9:30AfterBreakfast
10:00Before Lunch
11:00Before Lunch
12:00Before Lunch
13:00Before Lunch
14:00Before Lunch
14:30After Lunch
14:45After Lunch
15:00After Lunch
16:00After Lunch
17:00After Lunch
18:00After Lunch
18:30After Dinner
18:45After Dinner
19:00After Dinner
19:30After Dinner
View 2 Replies
View Related
Sep 30, 2007
I want to create an event log by which I am able to track changes by the users. we work on LAN and any body can write on the workbook. my work is to check for discripencies. If I get any type of help by which I can track the user name, date, cell that has been changed and the PC address. I got this code from ozgrid. in place of "c: racker.text", I used the path where to place the event and I was successful.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Locked Then
TrackFile = "C:Tracker.txt"
TargUser = Application.UserName
TargAddr = Sh.Name & "!" & Target.Address(False, False)
TargVal = Target.Resize(1, 1).Text
TargDate = Format(Now, "yyyy/mm/dd hh:mm")
x = TargDate & vbTab & TargUser & vbTab & TargAddr & vbTab & TargVal
Open TrackFile For Append As #1
Print #1, x
Close #1
End If
End Sub
I want to get the PC address from where the changes were made.
View 2 Replies
View Related
May 10, 2014
I am trying to create a Macro that will let me copy and paste the Inventory with the Corresponding SKU on a different spreadsheet, I have attached the Spreadsheet, Sheet 1 is the Missing Quantities and Sheet 2 has the updated Quantities that will need to be identified by SKU from Sheet 1 to Sheet 2.
View 1 Replies
View Related
Sep 28, 2009
I'm trying to make a simple program to allow me to total the amount of air filters I need to purchase for a given month. I am entering the amount of filters for each property and the frequency of replacement ie. monthly, quarterly, one month out of the year, etc. I would like to enter a month, ie. 2 for february and have the program list the quanty of each filter that I need to purchase for the given month. See sample attachment.
View 3 Replies
View Related
Jul 9, 2014
I created the following macro by recording the macro and going through the steps manually, however I need to make some changes and can't seem to accomplish what I'm trying to do.
The Macro opens a master inventory file, creates a new line, and then links certain column cells in the inventory to corresponding places within the original form (the macro is executed from the original form once it's completed).
Problem is, the macro is written using the form "template" so whenever I save the template as the name of the unique item, it won't update the macro language as well.
What I'm trying to accomplish is when someone opens the template, the save immediately with a different file name, and once the form is completed and the macro is run, it's creating the new line in the inventory pointing to that specific file.
I thought somehow utilizing ThisWorkbook within the macro instead of explicitly using something like
"='[Control Sheets (JOHN TEST).xls]FUND SET_UP PG_1'!R2C3" would accomplish what I'm trying to do.
View 5 Replies
View Related
Dec 16, 2011
So I'm trying to create a balance ledger to track my transactions at different locations.
This is basically what I have:
C4 = numerical value for site A
D4 = balance for site A
E4 = numerical value for site B
F4 = balance for site B
G4 = total balance of both sites
Values for C4 and E4 are manually entered.
D4: =IF(OR(ISBLANK(D3), ISBLANK(C4)), "", D3+C4)
F4: =IF(OR(ISBLANK(F3), ISBLANK(E4)), "", F3+E4)
G4: =IF(AND(ISBLANK(D4), ISBLANK(F4)), "", D4+F4)
I have these formulas auto-filled to the bottom of the sheet of each column. The problem I'm having is that with this setup, the return on the G column is giving me
#VALUE!
for all rows that do not have any values entered yet. Is there any way to fix the formula in column G so that it reads the value of the cell instead of the formula in the targeted cell?
I am using Office 2010 on Windows 7.
View 2 Replies
View Related
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
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
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
Apr 28, 2013
I've used excel to do a lot of managing stuff as far as budgets. I'm not sure if this can be done or how. I'm not to great in excel, So perhaps you can lead me a hand. I'm starting out a home based business and trying to set everything up so I'm ready to officially start. I am planning on getting a scanner to keep track of what comes in and out.
So what I wanted to try was if I scan "A" 4 times I wanted the QTY to be 4 so basically group similar Items move on to "B" and oh crap I found 10 more of "A" scan that and have it updated now to 14. You catch my drift? so I'm not sure if a statement like {if "A" is scanned then the QTY would be +1} Then the sales part would be the opposite of that statement.
View 1 Replies
View Related
Jan 13, 2014
I am making a sheet to track the daily cycles of a machine. I need to get the information from the "meter reading" column to populate in the " cycles recorded" cell. I'm very new to this and need a formula to use to track the changes in column D on the attached sheet that will populate changes in cell D4.
Cycles counts.xlsx
View 4 Replies
View Related
Sep 27, 2009
I'm trying to have an excel sheet that will keep track of teams. In Col A will be the name, Col. B is a score, Col C is # of wins, Col D is # of loss's. The range for the list will be A3 to D153. Now starting in M3 I want to beable to list the persons name and have the sheet pull over the original score and place it in N3. Once I mark if they win or lose in O3. I would then like the sheet to if they win add 4 points or if they lose subtract 4 points from thier score in Col B. I have use the VLOOKUP to be able to pull the scores from col B, but I'm not sure how to change the scores.
View 9 Replies
View Related
Jun 10, 2009
DISCUSSION
I have a spreadsheet that I have been creating for work. It involves a UserForm (FRM_TubingTransfer) and writing the data entered onto sheets (either the ‘MASTER SHEET’ or ‘COMBOBOX DATA’) in the same workbook.
PROBLEM
I have (with your help and much trial and error) gotten the workbook to a reasonably working model. The only major thing left is the ‘AVAILABLE TUBING’ sheet. This sheet is the most complicated, I think, because it isn’t just strictly writing the data.
First, any time there is a NEW PROPERTY / SUPERVISOR entered in the form onto the COMBOBOX DATA screen, the code needs to check the AVAILABLE TUBING sheet and check if that data has already been entered there (This is because there may be some instances where the PROPERTY NAME, and PROPERTY NUMBER are the same but have a different PROPERTY AFE. Aka, there may be two entries with ‘WELL 1’ that have different AFE numbers but they are the same location and need to be treated as such.). Also, if a pipe yard (denoted by “-YARD-“ in the Property number and AFE number column) is entered, do not enter it on the AVAILABLE TUBING SHEET.
Second, when a TUBINGTRANSFER is processed, the program needs to do several things;
For the FROM LOCATION (if a well and not a pipe yard)
Add Buried Joints to the buried cell
Add (E) to the damaged cell
Subtract (A), (B), and (C) from the total on location cell
For the TO LOCATION (if a well and not a pipe yard)
Add (A), (B), and (C) from the total on location cell
View 11 Replies
View Related
Jan 16, 2007
I have the following code what it does, is when a user changes a cell it copies the row to another sheet along with the userstatus. Problem is if the user pastes more than one column of data into the sheet it copies the row more than once depending on the amount of columns the user pasted . I only want to copy the row once.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SelRng As Range
If Not Intersect(Target, Range("A3:K30")) Is Nothing Then
Set SelRng = Target
Application. ScreenUpdating = False
For Each cell In SelRng
Range(Range("A" & (cell.Row)), Cells(cell.Row, Columns.Count).End(xlToLeft).Offset(0, -5)).Copy
Sheets("Tracking").Select
ActiveSheet.Cells(Rows.Count, 31).End(xlUp).Offset(1, -13).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 13).Value = ActiveWorkbook.UserStatus
Sheets("Engine 1").Select
Next cell
Application.CutCopyMode = False
Application.ScreenUpdating = True
End If
End Sub
View 7 Replies
View Related
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
Dec 17, 2013
I am trying to create a spreadsheet that will keep track of budget spent and budget remaining.
I need the values to take what is spent from expenses claim forms that are filled out,
Each budget category has a code and within that code there are sub heddings
View 2 Replies
View Related
Mar 23, 2013
I am trying to build a sheet to track deliveries into the company I work for.
We book loads to come in at set times for production, I am wanting a sheet which I can enter the due time and the actual time of the the different companies and then produce a report in graph format to see which are the worst at late or early deliveries.
View 2 Replies
View Related
Jul 22, 2014
I have a spread sheet for my checking accounts. There are 3 sheets, BW, QW, and Chase CC. In the 1st 2 sheets row D is named "Category" and the 3rd sheet has the same name but is in column C.
How to link the 3 sheets to a summary sheet keeping track and totals of the categories ?
Example in sheet BW I have a category called "Utilities" and the column to the right of that is the amount. Then ect for other categories.
View 2 Replies
View Related
Oct 26, 2008
Following is an example of an HLOOKUP formula I'm using:
=HLOOKUP(A1,$B$2:$E$5,4,FALSE)
The problem I'm having is that if a user inserts a row within the range B2:E5, then it throws my lookup formula out, because the formula should now be looking at row 5 and not row 4. How do I get the number 4 in the formula to be a variable that always selects the last row of the specified (variable) range?
View 3 Replies
View Related
Jan 9, 2009
i'm using a macro in excel 2007 that will open a Microsoft Project file, and go to the filtering menu. This is working fine however i have come to a deadend as i need the answer from a Data validated menu to be inserted into a box that project creates. Basically i need to copy the text from my excel (worksheet 2, cell c2), into my vba formula so that it automatically inserts into the MS project box. I hope this all makes sense and would be massively gratefull if you could point me in the right direction.
View 9 Replies
View Related
Jan 14, 2009
So I've got Sheet 1 with say
____A___B___C
1___m___i___c
2___r___o___s
3___o___f___t
I would like to create a button that can create a new sheet and paste A1 to C3 at the same location on the new sheet
and I need this to create a new sheet and do that everytime the button is pressed.....
View 11 Replies
View Related
