Is there any way to capture the comment that comes when we use "Track Changes", the date & time when the cell content changes should be retrieved in the adjacent automatically. Is there any way to do it using formulas/ VB code.
View 7 RepliesI tried to use Formulas -> Trace Dependents to find which cells is linked to the current cell.
Excel only shows it is linked to the other tab, but don't tell me which tab exactly. How to find the tab which is linked to current cell?
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.
I am using Excel 2003. Looking for a way to find out whether some data I want to delete in a sheet is used by a formula somewhere else within the file. Is there a way to check that? In other words, I have the number 500 in A1, and I want to delete it, but I don't know whether it will impact a calculation on another sheet that involves that value in A1.
View 4 Replies View RelatedI have a list of names and corresponding costs charged for each entry. The names appear multiple times for multiple costs charged, but contain any combination of characters after their name.
I am looking to get the sum of all entries for each name. I have tried the following formulae, but it does not return any information:
=SUMIF('Jan line items'!E:E,(CONTAINS:A:A),'Jan line items'!F:F)
I have two sheets....one sheet has a all the detail I need on it. On the second sheet I am trying to pull all the detail by category but want to eliminate any zeros in between. Anyone know how to do this?
I feel like I am being very vague so if I need to give more detail just let me know.
I am trying to re-jig a formula that shows hours worked for a particular
day, to showing any absence detail shown for a particular employee. I have
the fllowing formula which doesn't work and I'm not sure why. A similiar
formula works for me getting basic hours worked etc
=SUMPRODUCT(--(DailyDate=F67)*(StaffNumber=$C$11),(AbsenceDetail))
My range names are correct and all have the same 'length'. F67 is 09/05/05;
StaffNumber = 100 and the value that is within the AbsenceDetail cell for
the 09/05/05 for # 100 is Holiay, except my formula returns 0 (I have it
formatted as General)
I have a spreadsheet that has detail and summary info. I want to update the summary with the detail. I currently have the summary next to the detail, but I may move it to it's own tab.
View 6 Replies View RelatedI want to write a formula so that if an item in column A = " name" and column B = " date" (name and date to be selected from a drop-down box), then it will return the adjacent cells text in column C.
For example;
ColAColBColC
nick01-Marabc ltd
mick01-Mardeliver it
dan02-Mardiy
nick03-Margo ltd
mick04-Marfreebie
dan03-Marhelp ltd
If I select "nick" and "3/3/08", then the formula will return "go ltd". I know how to do filters, my aim is to create a master sheet and then for someone else to be able to select their name and a month. The month will then update a mocked up calendar (i.e. a 7 by 5 sheet where cell A1 = Date selected, A2 = Date+1, A3 = Date+2 etc). I've attached a basic mockup of what I want to do. If I select nick and march, the yellow highlighted cells should populate in C9 and E9 with abc ltd and go ltd, the others blank.
Recently our IBS provider change something in their data. As a result after the export I can no longer use
HTML Code:
Set PTCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=Range("A1").CurrentRegion)
I had to change the SourceData to:
HTML Code:
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange.Address)
Since I have done this when I get to this statement
HTML Code:
Cells(FinalRow - 1, FinalColumn).ShowDetail = True
It no longer creates "Table1". Which sends a Subscript out of Range error on this statement
HTML Code:
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=10, Criteria1:= _
">0", Operator:=xlAnd
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=12, Criteria1:= _
"
When you double click on a number in a pivot table data area you get a new sheet with that information showing the detail, is there anyway to "trap" this action?
I want to run a macro on the detail data but can't see a way to do it automatically, have tried, BeforeDoubleClick, PivotTableUpdate, NewSheet events but all have there draw backs if you are doing something other than showing the detail for the pivot table. Of course I can just run the macro after the sheet is made but it would be nice to do it on its own.
I have recorded the below macro to select a pivot table field (on demand) and show the information on anthother sheet tab.
Obviously if the order of the fields change then the macro is buggered, any ideas so it only selects the on demand field and shows that info.
Sub Macro1() ....
When you create a pivot table and double-click on the output of the pivot repot you access the detailed info that make up this particular number.
Is it possible to preserve the format of the raw data file that has been used to create the pivot table output?
For instance, the raw data has $. When I create the pivot table I can format the table to reflect the $. However, when I double click on the pivot table number where I have the $ I access the detail info but the format (in this case $) is lost.
I called Microsoft and told me it is a flaw in Excel. But, I'd like to check with you before I give up.
If I can't do that. I have an alternative: double click on all the numbers of the pivot table then format the sheets to include the $ and then create a hyperlink from the pivot table to the detailed report tab.
I use the GetObjects function to gain access to the properties of a mailbox and want to fill out a table with some detail information. The code works fine until I have a mailbox which name has an apostrophe included.
Set o = getobjects("winMgmts:!\VS1
ootMicrosoftExchangeV2:Exchange_Mailbox.LegacyDN='/O=TEST/OU=TEST/cn=Recipients/cn=co'brien',MailboxGUID='{59A40254-9F12-4502-9A03-3FB5CB0BBD38}',ServerName='VS1',StorageGroupName='sg1',StoreName='mb1'")
I highlighted the section that causes the problem. The string within the string is encapsulated in apostrophes, hence the apostrophe in the mailbox name co'brien confuses the code and raises an error. Microsoft provides a solution by using escape characters, but that seems to work only in vbscript, not in vba. Here is a link to Microsoft's solution:
http://www.microsoft.com/technet/scr...4/hey1217.mspx
I tried some workarounds that are being used for a similiar problem with SQL statements as well, but again without success - these are the versions I tried:
co/'brien
co'brien
co''brien
I am trying to track a score. A master capacity sheet has two columns with identifiers (upstream and downstream nodes) relative to a pipe segment. I have a separate sheet (condition sheet) with the corresponding scores. I want to enter the upstream and downstream node numbers in 2 columns (A & B) in the master capacity sheet, and based on the the upstream and downstream node numbers, have the corresponding score (from the condition sheet) on that row copied in the master capacity sheet.
View 6 Replies View RelatedI have a some detail in the tabular form with different criteria as size, colour ,purity,cut ,by combining all these i have price list in a tabular form , if I want to intersect all this and find the price how can i do so
View 9 Replies View Relateda query as i'm not too good with vba codes but can record macros but not for what i need.
I have got a pivot table which has about 200 rows. In Column A is the provider and in column B is the total number of people.
I now need to create a sheet per provider and the manual way to do is to double-click on the numbers.
Is there a code that would this automatically?
This file contains 2 sheet Main M and Detail D.
In Sheet Detail D, When I choose Code, month of Project, it will be shown the result.
By the way, tell me how to delete the Auxiliary column in Main sheet.
I have an excel sheet containing data as given below, wherein we receive certain report from each department on monthly basis and the same is mentioned as received against each month under header column. We need to remind the department about non receipt of report for particular months which contain empty cells. Macro is required to generate the report of empty cells mentioning the months against each department.
Available Data
Dept
Sep-12
[Code]....
[Excel 2003] I have 2 spreadsheets: one to summarize data from a 2nd detail spreadsheet. I'm analyzing work order information for a service operation.
I'm using dynamic name ranges, as follows, for the detail:
WO_Num =OFFSET('WO Tracking Log'!$A6,0,0,COUNT('WO Tracking Log'!$A:$A),1)
Other detail data is defined as these examples show:
GM_X =OFFSET(WO_Num,0,8)
OpenDate =OFFSET(WO_Num,0,1)
All detail data begins in Row 6 in the detail spreadsheet.
In the Summary spreadsheet, it appears to make a difference where my calcs are located in order for my COUNTIF's to work correctly. As long as I keep my summary calc (to total the number of work orders in the detail) in Row 2 of the Summary, it works fine...but if EITHER I move my calc down a row OR if my detail drops down a row because a row was added above (where I have just header info), my summary totals change?!?! I don't understand.
Here are two examples of the calcs I'm using in the summary:
=COUNTIF(WO_Num,">0")
=COUNTIF(GM_X,"X")
Can someone tell me what is going on? What I'm doing wrong?
I have a pivot table that has about 20 categories of Asset Numbers as a filter. I use the show pages function to create individual sheets for each category, but I don't want pivot tables to be the output; I want the detail from the pivot table (the rows that are displayed when you double-click the grand total). I have been messing around with various macros, but can't seem to find the right commands to get this output.
View 9 Replies View RelatedSo 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 added a worksheet named Log
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.
For example:
Item#1 5/3/05 7/23/06 8/27/06
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?
View 9 Replies View RelatedI 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?
Quantity Tracking.xlsx
I was wondering if there is a function to be able to track when the last time a hyperlink was clicked and display the date in a new cell?
View 1 Replies View Relatedi 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
1021-08-2007 10:491-Ny
1022-08-2007 15:381-Ny03- Under vurdering
1004-01-2008 13:4703- Under vurdering10-Lukket
10029-08-2007 10:051-Ny
10003-09-2007 14:121-Ny04- Afventer tilretning
10012-09-2007 15:2204- Afventer tilretning10-Lukket
100006-02-2008 11:2901-Ny
100012-02-2008 15:0501-Ny04- Afventer tilretning
100015-08-2008 08:5304- Afventer tilretning10-Lukket
100015-08-2008 22:1310-Lukket04- Afventer tilretning
100004-02-2009 14:0404- Afventer tilretning10-Lukket