How to track the value of last item in CountIf and work backwards? I am trying to do inventory valuation at the end of the month. For example I have 7 purchases in a month for 1000 units in each purchase order, my on hand inventory is 1500 units. from the purchase details I did a countif to tell me there are 7 PO's in a month for the item, but if i want to find how much each units costed starting from last PO in the CountIf is there a way to pull that number? I have total purchase amount by PO & quantity by PO also in separate columns, so I know cost per unit in each PO but can't figure out how to go backwards (starting from last in)
I am trying to track metrics for a QC dept. Right now my sheet has 35 lines of data and a header row, for 36 lines total. This is my funtion that isn't quite right:
proposedenter is just a named range. I can see this is wrong but how do I base the count criteria on the left 7 characters of an item that is matched in the range?
VB: 'enables user to click [U]highlight and select[/U] an item in ListBox1 and ListBox2 item (same row in index) is also [U]highlighted[/U] (highlighted only not selected) Private Sub ListBox1_Click() ListBox2.ListIndex = ListBox1.ListIndex End Sub
Question: Is it also possible to enable a user to click to select an item in ListBox1 and ListBox2 item is also selected simultaneously (same row in index). Is there excel vb code to do this?
I think the code may be along the lines of the ListBox SelectedIndex property. What would be the Excel VB code equivilant for the ListBox SelectedIndex property, if so?
I need some way that can identify when the item in the description column doesnt match the first item of the same number- for example, here the 1-blue and 3-orange would be flagged because they should match the 1-yellow and 3-green.
I need to do this on a much larger scale (approximately 20,000 data points), so I wanted to create a formula or macro that could do this for me.. I thought making a reference page with would work but I keep getting an error.. I haven't done VBA in a while, so I may have syntax errors.
If Range("A2:A9").Sheets("Sheet1") = Range("A2:A6").Sheets("Ref") And Range("B2:B9").Sheets("Sheet1") = Range("B2:B6").Sheets("Ref") Then Range("C2:C9").Sheets("Sheet1") = "x" End If
I am getting close to finishing the drop down menu capability when filling in column L in tab Transactions. However, there is a snag. When I enter part of account say "fin" (the important part here is that the part of the word should not be the beginning of the account name) - then I select an account from the menu - but it does not stay in the cell if the part of the name is the beginning of the account name - all is fine.
I am using a vlookup and have a problem. I am assigning a category to an item number based on the first two characters of the item number. For example item number 60123 would equal scrap because of the first two characters of 60. But the item number can begin with either a number or letter. Here is the formula I am using that works for item numbers that begin with numbers:
=VLOOKUP(VALUE(LEFT(E2,2)),Sheet3!A:B,2,FALSE)
It works fine until I reach a item number that begins with a letter, then I get the dreaded #Value error. If I take the value out of the formula then it works for the letter based number items but not for the number based item numbers.
Here I would like to get your valued guidance for the usage of "IF" formula. I am having two sheets one is new XL-1 and other is old XL-2. I want to have a item number from the old file corresponding to the same item descprition in the new file. I am attaching the snap shots of two files which is more explanatory than describing in words here.
I am using Vlookup to compare our item number with the suppliers item number but the result is removing the decimal point on some but not all items.I have included a small sample but it concerns almost 10.000 items.
So I got this code from http://www.ozgrid.com/VBA/track-changes.htm i followed the directions and pasted it in the workbook module but does not seem to be working. I am thinkning the copy and paste feature is messing something up anyone know what?
I have a list of items that I keep track of when they were last used.
For example: Item# Last Used Item#1 8/27/06 Item#2 5/2/07 Item#3 6/30/07
What I would like to do is when I enter a date, it automatically tracks the changes so that I can not only see the most current date but all previous dates if I need to.
The ideal would be to transfer it to another worksheet so that is looks like the original except that is shows multiple dates after it instead of just the most recent.
We have an excel file that I've developed that people in our department are supposed to be using - not that they want to - but it is an edict from our bosses. Is there a way to track who is actually using that file so I can verify that they are using it rather than just relying on them saying that they are?
I have a work sheet that has 113 tabs and I use two macros (both written with help from this forum - thanks), one to protect all of the worksheets and the other one breaks the workbook into 113 separate files. I want to use the tracking feature because I will be sending out these budget templates and want to identify the changes when I receive them back.
Problem is once I enable the tracking I can no longer run my macro’s; I get a VB dialog box with a red X and the number 400. Anyone know how to work around this problem?
I have found another user's code that will track changes for a single sheet, if that code is in the sheet. This code uses the Worksheet_Change function. However, I have just stumbled acroos the Workbook_Change function. The workbook function is exactly what I need, since I have multiple tabs that need to be tracked. However, the code does not seem to work. I can switch between the two functions, using the same code afterwards, but with the new workbooks phrase it does not work. I tried placing the Workbooks_Change in the "ThisWorkbook" object in VBA, but it does not seem to work there or anywhere else.
I have attached the file. The code is in the "thisworkbook" function.
For quick reference:
Private Sub Workbook_Change(ByVal Target As Range) Dim r As Long, OutSht As Worksheet
Set OutSht = Sheets("Log") r = OutSht.Cells(Rows.Count, 1).End(xlUp).Row + 1 OutSht.Cells(r, 1) = Target.Address OutSht.Cells(r, 2) = Now OutSht.Cells(r, 3) = Environ("UserName") OutSht.Cells(r, 4) = Target.Value OutSht.Columns("A:D").Columns.AutoFit End Sub
I am currently trying to find a better way to track quantities of delivered material from multiple suppliers. I have a attached a sample of what I am trying to do. In the "totals" sheet Row 3 is working as planned. There are a couple of issues that I am having with my formatting as is. First off in my "raw" data sheet there is an empty row between each row of numbers (this is they way I am sent the information from the supplier). This empty row causes my totals to place a Q everywhere column A on "Raw" is not filled with "B". Is there a way to have the totals sheet only pull from the cells with values in them?
My other question is how I would be able to get the values for material, Net wt., and Charges to populate if Column A in "Raw" is Q?
i am a builder and i want to make a spread sheet to keep track of what i have been paid and the labour costs i have paid out
so i am working on 20 houses that all have 7 stages of payment each, and 5 to 8 men working on them who receive a price for each stage, but are paid a portion of that price each week while they are working on that stage
what i am trying to do is set up a sheet to keep track of what has been paid and the balance remaining for each stage and what has been received
i have tried doing it a few different ways but im none to clever with spread sheets and what is causing me the problem is next to each payment made i need to put the date and the name of the person who was paid so it always seems to just look a cluttered mess
have any of you got any ideas of how i could simplify this and make it look neat and easy
or alternatively is there some other software that may suit this application better?
So if we take first line the defect id is 10. The date entered is 21-08-2007 and the status is 1-NY ( means new) NExt line the defect ID is still 10 the date is changed to 22-08-2007 and the previous status is was 1-Ny and the new status is 03- under vurdering (to be evalueted) Third line the defect ID is still 10. The date is changed to 04-01-2008. The previous status was 03- Under vurdering ( to be evalueted). The new status is 10-lukket.
I want to be able to track the time from 1-NY to 10-Lukket.
Now the even more tricky part. As you can see other defects go over different statuses e.g defect number 1000 here.
The sheet I have is 13000 rows and I have a total of 2300 defects
i m trying to use Excel program to keep track of the hours he is working. I know there is a way to do it but it has been many years since I used Excel and can not remember how to do it. He would like to insert the time (hours & minutes) he works in column A and have a sum in the next column.
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.
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.
I am trying to track installation of equipment in excel, I know what I want done, but not how to do it. I have 8 columns, and I want a yes in each column to equal 12.5% and a column to show progress at the end, I also want to show total progress for installation of all equipment at the bottom of the page
I've been looking around for a way for Track Changes to reference the loggedin username of the workstation rather than the name in Excel registration.
I found this Function UserNameWindows() As String UserNameWindows = Environ("USERNAME") End Function
: on this board and another blog.
But I don't know how to enter it to make this work. I had read you make it a new module and assign a formula, but the most I could get through was opening the VBA editor for the workbook, creating a new module, and pasting the code. Changes would still reference the Excel registration name, not the Windows' logged in username.
I was wondering if VBA or Excel has the capability to add and total Row Height or Pixels? For example I have my Rows set to a height of 14.25 (19 pixels). My Range that I am working with is A2:T41 for a TOTAL ACCUMULATED ROW HEIGHT of 570 (40 Rows X 14.25) and 760 Pixels (40 X 19).
What I would like to accomplish is that once the TOTAL ACCUMULATED ROW HEIGHT of my Range reaches 570 then Row 1 appears. If the TOTAL is less then 570 then Row 1 remains Hidden.
Does anyone know IF and HOW this can be done? I need this due to the fact that the Rows will Autosize and I need to have my header Row appear. I can't just Freeze Row 1 because my actual Headers are on Row 16.
Hope this makes sense and hope that someone has a solution.
I have created a spreadsheet for my students on a shared drive. They will be able to go there and make comments..My question is this: I know they can have their name set in Excel...How long will the name stay in it. I have to change the speedsheet daily. Does that mean the students will have to enter their name daily. Also how can I track each student comments on the spreedsheet?
I'm trying to put together a basic spreadsheet to track offensive plays for high school football. I'd like a formula that will change the LOS (Line of Scrimmage) to reflect the yards gained on the previous play. The problem is that you'd probably need negative numbers up to 49, and then back down to 0.
I created a financial forecast model from scratch. The model is self-contained and has no external links.
Backgrounder
Before firing a question at you, let me explain briefly how the model works. I have an assumptions tab, several costs tab from various departments that ultimately feed the Results tab (target price on a monthly basis) . Let's assume that my target price is on line 200. To me more specific, for Jan09, the target price is on B200, Feb09 on C200 and so on.
Management will feed the model themselves, given that they are assigned specific areas that they can input their forecast.
Issue or question to you
I would like to track historical changes to the price on line 200 on my target page. That is for every individual change and their impact on price.
To illustrate this let assume the following:
Let say User A made several changes on his forecast, I would like the macro to automatically record the individual change that has been made and the impact it had on the target price on line 200.
Assume that User A made two changes on his forecasting sheet. He increased the cost of supplies by $50,000 (on one cell) and increased the cost of labour by $10,000 (on another cell).
What I would love to have is a macro that would record the changes that User A changed; (i.e. cell, tab, user) after he entered the $50,000 and after he entered the $10,000. This would be recorded on a separate sheet.
Thus, immediately he entered the $50,000 on the cell and press it would record the change and the impact it had on price on the target page. Then when he entered the $10,000, it would do the same.
The format of the "changes sheet" that would contain the changes would look something like this:
USER..Date..Cell changed...Tab..From value..To value. Price Before Price after Impact (Price before less price after)
I am aware that there's a lot of details but if you don't understand the issue, it's very difficult for you to resolve.
I assume that it requires a VBA code of some sort to set this up. Given that I am not familiar (a true dummy) with VBA, I would need like a step by step instruction.
