Tracking Date Of Last Phase Change In Project Steps
May 19, 2014
I have a project with over 600 steps which we are trying to track when each part progresses by having 52 weekly columns. Their are 8 users which we have allocated a colour so when they complete one of the steps they select which step they have completed and which colour they are. This works well but I would like to put the date that the change was made in a separate cell in that row.
My question is can this be done with a formula as I am in favour of doing this as soon as someone changes a cell in the week range. If not I will need to nut through a VBA sub routine.
I know that this formaul does not work but it is along the lines of what I am testing for: =if(sum(T55:BM55)>" ",Date(),0) where if any of the cells in the range T55 to BM55 have any value or letter in them then put todays date in cell L55 else do nothing.
This is a project plan with tasks and dates. Column A is the activity number. (Example 1, 2, 3" etc). Column B is the task (Ex. "Complete Report"). Column C is number of days required to complete the task. Column D is the dependency column. (Ex. Cell D2 =1 in other words Task 2 is dependent on task 1). Column E is the date.
I would like to have a seperate start date cell and a go live date cell.
The objective is to enter a start date, and have each column E date increase based on the number of days entered in Column C. If a task is dependent on another and I change the number of days in Column C I need the dependent task to change the same amount of days.
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
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.
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.
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.
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.......
i work dispatch at a company and we're trying to track how long it takes for a technician to arrive on-site. my worksheet currently includes the date of call-in (E1) , time of call-in (F1), Service Date (G1), Service Time (H1), among many other field. the easy solution is to use the formula (G1+H1)-(E1+F1), and use the answer as the total amount of time, but the problem is if a customer call in at 3:00 PM and we service them at 9:00 AM the following day, it looks like it took 18 hours to arrive on-site, when in reality, since we close at 5:00 PM and open at 9:00 AM, we only took 2 hours to arrive on-site. is there any formula i can write to account for non-business hours and weekends, or is this a bit above what i should expect from excel?
I have a table of projects with 1) duration in year, 2) time window (number of years of our planning cycle), and 3) start year of the project. I want to generate a list of project parts of all projects where they may take place. This will serve as an input to an optimization program.
So a project of 2-year duration should have 2 parts over any year within the time window. I am including the "impossible" ones for my developer to tag them as "0" when we run it through his code.
My task is to consolidate 4 Excel Project Lists (Workbooks) to a Master Workbook. The Project Lists has a different structure and almost different content. The relevant information is always on Sheet1 but it has completely different ranges. The only constant is the Project Number, which should be used to sort the information. Every Project should be listed only once with all the existing information.
I found a code written by Ron de Bruin which has already some components that I want to have in my VBA but I think there are still a lot of necessary adjustments to do.
Code: Sub MergeSelectedWorkbooks() Dim SummarySheet As Worksheet Dim FolderPath As String Dim SelectedFiles() As Variant Dim NRow As Long Dim FileName As String Dim NFile As Long Dim WorkBk As Workbook
[code]....
The Master Project List should has the headers in Row1 and the information listed below. The Macro should automatically places the correct information to the correct column. Some of the information are in 2 or more of the lists but they should be listed only once in the Master List.
Project Number
Project Description ... 1111E.000000001
[code]....
I guess a problem is that the structures of the Lists are quite different so there must be a kind of sorting process.
In the end I want to have an Excel File with the Macro and a Command Button and by clicking the Macro creates a new Workbook with the Master List.
It would be better if there is a variable range instead of a defined. Like the Macro searches the last row and starts at this row and column.
I am trying to get excel to open a MS Project file and then run a project macro on it.
What is happen is that when I run my code it works, and then excels VBA window freezes up and the run arrow goes away, and if I try to open project I just get a chiming noise and it won't open.
For now I have to select the MS Project file from a directory, but in the future I would like it to go through the folder and open all of the file in the folder
EXCEL CODE Sub ImportMSProject() Dim FileToOpen Dim mpApp As MSProject.Application Dim prjmacro As Object 'Identify the File to Open - START FileToOpen = Application.GetOpenFilename("Microsoft Project Files (*.mpp), *.mpp") If FileToOpen = False Then Exit Sub End If 'Identify the File to Open - END
I have project start date in cell C2((MMDDYYYY format).In cell D2 I have put the total days needed to complete the project.In cell E2:E6 I have got the scheduled Holidays.
I need to calculate the project completion date in F2.We work from Monday to Saturday,Sunday being off day.
I am creating a spreadsheet that will display the subject mentioned. I need a chart that displays project deliverables as labels along a year long date range. I want to be able to change the date range to any period of time by entering a start date and an end date and having the chart automatically update to that date range and display the project deliverables due during that range. I have used the following links and have been able to accomplish each of them individually, but have not been able to combine the labels of the project timeline link with the dynamic date range described in the second link.
[URL] .......
[URL] ......
How to achieve the spreadsheet I described using the two ideas presented in the link?
Attached is a spreadsheet wherein I'm trying to extrapolate project costs across various months based on working days in a month subject to start and end dates of the project. Need an accurate formula to spread the cost.
I need to write a couple of functions. Firstly I want to do a count on column B (project $) depending on the criteria on column A (project description). For example a total count of all values in col B if project description is "P" or "A". [I can do sumif functions but this is a countif and I cannot get this right].
I have written a macro, big cheers as it's my first, and it even works as I wanted it to.
Trouble is when you run it, it shows you all the steps it is making. Is there any way to stop this and purely for the end result to appear? It may well also have something to do with my PC needing more RAM to make it faster. Ever since upgrading (?) to 2007, my PC feels like it is stuck in quicksand.
I am trying to produce a list of numbers (preferred Sheets Function and not VBA).
Every group should be of 5 Identical Numbers - starting with 2 and step of 2. (An Example is shown in the attached picture).
I would like to produce that list with the help of ROW() and all available build-in Functions.
If possible, I will appreciate very much some explanation as for the principal of producing such a list when, for inatance, the step is 3 , starting from 3, while every group includes only 4 identical numbers and the first cell, to present the first value, is A11 and down to A26 - presented here in Horizontal layout: (3,3,3,3,6,6,6,6,9,9,9,9,12,12,12,12)
Basically, I just recorded a macro to change the cell value. This code continues to 4.8 . . .Is there a more efficient way to code this?? Also, I'd like to add a time delay (say +-5seconds) between each step. So basically it would say:
Range("A1").Select ActiveCell.FormulaR1C1 = "3.1" [CODE THAT DELAYS FOR A TIME, THEN GOES TO THE STEP BELOW] Range("A1").Select ActiveCell.FormulaR1C1 = "3.2" [CODE THAT DELAYS FOR A TIME, THEN GOES TO THE STEP BELOW]
I have 2 columns of data with 7,000 rows that I am trying to manipulate in the following way. The first column contains a list of car manufacturers (not really my example, but involves the same concept). The second column contains a list of different models of cars. For instance,
Ford | Taurus Chevrolet | Avalanche Saturn | Sky Ford | Explorer Honda | Accord Ford | Taurus
What I want is a list of 2 columns with UNIQUE car manufacturers in first column with a list of UNIQUE car models (comma delimited) in second column. So for this example, the first row could read " Ford | Taurus,Explorer". I don't want Taurus to appear twice. I got a lot of help from JBeaucaire from this post. I just can't add the ability to remove car models from the list also. Thanks in advance for all the help!
I have an excel spreadsheet that I am trying to format. Each time the spreadsheet is of the same format but with differing amount of numbers. I.e. sometimes it will be a set of 3, the next time might be 6.
I want to write a macro that will find a set piece of text, move it offset(-1,2) and then find the next one.
I am looking for a macro that works like the ASAP Utility (Transpose data from one column to several columns in steps). To elaborate the work done by the macro it should transpose the values in a column to the number of steps that is user defined (Using InputBox) that is if there are 103 values in the column and the user enters the number of steps as 24 then the macro should transpose the data up to 24 columns and the rest in the next row up to 24 columns and so on unless the complete data is transposed.
For more clarity refer the attached excel sheet or the "Transpose data from one column to several columns in steps" utility of ASAP Utility.
I have the onerous task of dividing up a series of steps in one cell into multiple cells. I tried the text to columns utility, but it only delimits once, not multiple times. Below is a data example:
"1) Load App 2) Login using Administrator Access details . 3) Click on icon 'Admin' on the right hand side of page. 4) click on subcategory 'Administration'"
"1) Load App 2) Login using Administrator Access details . 3) Click on icon 'Admin' on the right hand side of page. 4) Click on subcategory 'Administration' . 5) Click on any Organization name from 'Organization' section in the list. Or click on 'Add Organization' icon in section 'Organization' . 6) Validate field 'Parent Organization' in 'New Organization' or 'Edit Organization' form" "1) Load Documoto. 2) Login using Administrator Access details . 3) Click on icon 'Admin' on the right hand side of page. 4) Click on subcategory 'Administration' . 5) Click on any Organization name from 'Organization' section in the list. Or click on 'Add Organization' icon in section 'Organization' . 6) Validate field 'Tenant ERP Accessible' in 'New Organization' or 'Edit Organization' form"
1) Load App 2) Login using Administrator Access details . 3) Click on icon 'Admin' on the right hand side of page. 4) Click on subcategory 'Administration' . 5) Click on any Organization name from 'Organization' section in the list. Or click on 'Add Organization' icon in section 'Organization' . 6) Validate field 'DMZ license key expiration period ' by click on checkbox or by 'Uncheck' checkbox"
I have a table of data (total 142 rows). Column contains dates, in the format dd-mmm-yyyy.
I tried to filter using DATE FILTERS->EQUALS and in the custom filter window, I chose EQUALS then picked a date from the date picker icon. The date I picked was 5/4/2009 (this is May 4, 2009, formatted automaticall by excel as m/d/yyyy).
When I clicked OK, nothing showed up despite the fact that there are 6 occurences of May 4, 2009 (formatted as dd-mmm-yyyy in the data table)
So my questions are:
1. Is this due to the formatting?
2. Is there a way to change the date format supplied by the date picker?
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")
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?
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....
