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 RepliesSo 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....
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.
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.
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.
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.
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 RelatedI 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] ......
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 RelatedAaron 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
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 RelatedI 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.
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 RelatedI 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
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.
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 RelatedI 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.
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 RelatedI 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 ?
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 Relatedlocate 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 RelatedI 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
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.
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?
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 RelatedI 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