asked on Gannt Chart Time Line for RASCI Model
EE Professionals,
I am trying to construct, in Excel 2010, a Gantt Chart time line (Tab 2) based on some very specific Input (Tab1). I believe that a Macro vs. formulas approach is better because the order in the Input Tab may change dynamically and the record count (i.e. could be 10 Tasks, could be 100), is unknown and also changes.
SPECIFICATIONS:
In Tab 1, a Macro needs to look at Column A and determine there is a Task or numeric number (vs. a Blank) and if a whole number, then task = a Major Task; if fractional (e.g. 1.1, 1.2, or 1.11, 1.21, etc.), then it's an associated Sub Task.
ALSO,
In Tab 1, a Macro needs to also look at Column E and determine if there is the letter "R"; if so, then and only then should it post the Start and Due Dates to the Gantt Chart. This means that there may be a number of Tasks in the Gantt Chart that are incomplete...and that's OK. Only Tasks and Subtasks that meet the critieria of having an "R" in Column E on the Input Tab should have an associated time line set lf values and appear on the Chart. Only Major Tasks should appear without a Time Line on the Gantt Chart; all other Minor Subtasks will only appear if they meet the criteria of having an "R" in Column E.
Example File attached.
In advance, "thank you" for the assistance on this.
B.
RASCI-Gantt-Chart-v1.xlsm
I am trying to construct, in Excel 2010, a Gantt Chart time line (Tab 2) based on some very specific Input (Tab1). I believe that a Macro vs. formulas approach is better because the order in the Input Tab may change dynamically and the record count (i.e. could be 10 Tasks, could be 100), is unknown and also changes.
SPECIFICATIONS:
In Tab 1, a Macro needs to look at Column A and determine there is a Task or numeric number (vs. a Blank) and if a whole number, then task = a Major Task; if fractional (e.g. 1.1, 1.2, or 1.11, 1.21, etc.), then it's an associated Sub Task.
ALSO,
In Tab 1, a Macro needs to also look at Column E and determine if there is the letter "R"; if so, then and only then should it post the Start and Due Dates to the Gantt Chart. This means that there may be a number of Tasks in the Gantt Chart that are incomplete...and that's OK. Only Tasks and Subtasks that meet the critieria of having an "R" in Column E on the Input Tab should have an associated time line set lf values and appear on the Chart. Only Major Tasks should appear without a Time Line on the Gantt Chart; all other Minor Subtasks will only appear if they meet the criteria of having an "R" in Column E.
Example File attached.
In advance, "thank you" for the assistance on this.
B.
RASCI-Gantt-Chart-v1.xlsm
Microsoft Excel
Last Comment
Bright01
Do you want the gantt chart to automatically update itself when new information is added, or do you want to generate it by pressing on a button ?
ASKER
Akoster,
Thanks for jumping in.
Automatically would be preferred since it is really an output graphic.
Thanks,
B.
Thanks for jumping in.
Automatically would be preferred since it is really an output graphic.
Thanks,
B.
This would do the trick :
autmatically processing on data entry
the processing itself :
as in
RASCI-Gantt-Chart-v1.xlsm
autmatically processing on data entry
Private Sub Worksheet_Change(ByVal target As Range)
process
End Sub
the processing itself :
Sub process()
Dim taskID
Dim taskName
Dim taskType
Dim taskRole
Dim taskStart
Dim taskDue
Dim taskRow
Dim taskCol
Dim fromColumn
Dim toColumn
Dim firstDate
Dim target As Worksheet
Dim row As Range
'-- initialise
firstDate = CDate(Now)
Const targetName = "Result"
'-- remove existing result
On Error Resume Next
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Sheets(targetName).Delete
Application.DisplayAlerts = True
On Error GoTo 0
'-- generate result sheet
Set target = Worksheets.Add
target.Name = "Result"
target.Range("A2") = "ID.#"
target.Range("B2") = "Task"
target.Range("C2") = "Start"
target.Range("D2") = "Due"
target.Range("E2").Formula = "=E1+4"
target.Range("F1").Formula = "=E1+7"
target.Range("F2").Formula = "=E2+7"
target.Range("F1:F2").AutoFill target.Range("F1:X2")
target.Range("E1:X2").NumberFormat = "d-m-yyyy"
target.Range("A:A").ColumnWidth = 8.43
target.Range("B:B").ColumnWidth = 17.71
target.Range("C:X").ColumnWidth = 12.29
target.Rows.RowHeight = 15.75
target.Cells.Font.Size = 12
ActiveWindow.Zoom = 75
target.Range("E3:J17").Select
ActiveWindow.FreezePanes = True
'-- process first start date
For Each row In UsedRange.Rows
taskStart = row.Cells(6)
If IsDate(taskStart) And taskStart < firstDate Then firstDate = taskStart
Next row
target.Range("E1") = firstDate
'-- process input data
For Each row In UsedRange.Rows
'-- skip header & empty rows
If IsNumeric(row.Cells(1)) Then
taskID = row.Cells(1)
taskName = row.Cells(2)
taskRole = row.Cells(5)
taskStart = row.Cells(6)
taskDue = row.Cells(7)
If taskID = CInt(taskID) Then
taskType = "Major task"
Else
taskType = "Minor task"
End If
'-- fill result sheet
taskRow = target.UsedRange.row + target.UsedRange.Rows.Count
target.Range("A" & taskRow) = taskID
target.Range("B" & taskRow) = taskType
target.Range("C" & taskRow) = taskStart
target.Range("D" & taskRow) = taskDue
target.Range("B" & taskRow).Font.Color = -16776961
'-- locate 'R' entries
If UCase(taskRole) = "R" Then
'-- determine start column
fromColumn = 0
toColumn = 0
For Each Item In target.Range("E2:X2")
If fromColumn = 0 And taskStart >= Item.Offset(-1, 0) And taskStart <= Item Then fromColumn = Item.Column
If fromColumn > 0 And taskDue >= Item.Offset(-1, 0) And taskDue <= Item Then toColumn = Item.Column: Exit For
Next Item
'-- toColumn=0 --> item due before first column in gantt sheet
If toColumn > 0 Then
For taskCol = fromColumn To toColumn
target.Cells(taskRow, taskCol).Interior.ThemeColor = xlThemeColorAccent1
Next taskCol
End If
End If
End If
Next row
target.Range("A1").Select
Me.Select
Application.ScreenUpdating = True
End Sub
as in
RASCI-Gantt-Chart-v1.xlsm
whoops,
line 30 should be
target.Name = targetName
line 30 should be
target.Name = targetName
ASKER
Akoster,
Thank you! I just tested it and have noticed some slight problems. First the first tab refers to the "INPUT" Tab. So when you add the number in column A, it should tell if it's a whole or fractional number. I think the macro does this. Then when I add the task name/description in Column B and the dates in C and D, I thought it should populate the equivalent spot in the appropriate rows in both "OUTPUT" and "GANTT Chart" tabs. If there is an R in Column E in the INPUT Tab, it should then display the time (in blue) in the GANTT Chart. Right now, if I add another Major Task to the Input Tab, along with a Minor one, and the dates, nothing shows up in the GANTT Chart.
Does that make sense?
Thank you for your patience on this.
B.
Thank you! I just tested it and have noticed some slight problems. First the first tab refers to the "INPUT" Tab. So when you add the number in column A, it should tell if it's a whole or fractional number. I think the macro does this. Then when I add the task name/description in Column B and the dates in C and D, I thought it should populate the equivalent spot in the appropriate rows in both "OUTPUT" and "GANTT Chart" tabs. If there is an R in Column E in the INPUT Tab, it should then display the time (in blue) in the GANTT Chart. Right now, if I add another Major Task to the Input Tab, along with a Minor one, and the dates, nothing shows up in the GANTT Chart.
Does that make sense?
Thank you for your patience on this.
B.
Strange, at my workplace this happens :
when I enter a whole number ID, the id number and "major task" is inserted. When I then insert start and due dates, they will be added as well. When both dates are entered, the corresponding fields are colored blue.
pay attention though that currently the macro code used only colors the fields when the dates are working days, e.g. in between 27-6-2011 and 1-7-2011. When a start day or due day is placed in a weekend (such as 2-7-2011) indeed the coloring will not work.
when I enter a whole number ID, the id number and "major task" is inserted. When I then insert start and due dates, they will be added as well. When both dates are entered, the corresponding fields are colored blue.
pay attention though that currently the macro code used only colors the fields when the dates are working days, e.g. in between 27-6-2011 and 1-7-2011. When a start day or due day is placed in a weekend (such as 2-7-2011) indeed the coloring will not work.
ASKER
I'm still having trouble;
1. How do I get the lines to number themselves? I have two lines that say Targetname and you asked me to change one to target.name=targetname; but does that have any quotes around it like the code below?
Const targetName = "Result"
'-- remove existing result
On Error Resume Next
Application.DisplayAlerts = False
Application.ScreenUpdating
Sheets(targetName).Delete
Application.DisplayAlerts = True
On Error GoTo 0
'-- generate result sheet
Set target = Worksheets.Add
target.Name = "targetName"
2.) If a date falls on a non-working day, does the color coding pick up on the first work day until end date?
3.) When you put in the name of the task in the INPUT Tab, it should show up in the Result Tab B.
I hope these are minor issues....
Much thanks,
B.
1. How do I get the lines to number themselves? I have two lines that say Targetname and you asked me to change one to target.name=targetname; but does that have any quotes around it like the code below?
Const targetName = "Result"
'-- remove existing result
On Error Resume Next
Application.DisplayAlerts = False
Application.ScreenUpdating
Sheets(targetName).Delete
Application.DisplayAlerts = True
On Error GoTo 0
'-- generate result sheet
Set target = Worksheets.Add
target.Name = "targetName"
2.) If a date falls on a non-working day, does the color coding pick up on the first work day until end date?
3.) When you put in the name of the task in the INPUT Tab, it should show up in the Result Tab B.
I hope these are minor issues....
Much thanks,
B.
the line target.name = targetName sets the name of the target worksheet (the text you see in the excel tabs) to the content of the targetName variable. This targetName variable contains the text "Result" as set on line 18 :
when youadd quotes such as target.name = "targetName", the target worksheet will be named "targetName". This is no problem but should then also be used on the delete line :
Sheets("targetName").Delet
When you post pieces of code, you can enter them in the 'add your comment' section below the thread, select the lines and press the small 'CODE' button just above the text frame that you use for typing in your comment.
line 1
line 2
line 3
will then become
which is displayed as
Const targetName = "Result"
when youadd quotes such as target.name = "targetName", the target worksheet will be named "targetName". This is no problem but should then also be used on the delete line :
Sheets("targetName").Delet
When you post pieces of code, you can enter them in the 'add your comment' section below the thread, select the lines and press the small 'CODE' button just above the text frame that you use for typing in your comment.
line 1
line 2
line 3
will then become
{code}line 1
line 2
line 3{/code}
which is displayed as
line 1
line 2
line 3
Currently, if a date falls on a non-working day, it will not be colored at all.
The way I looked at the example, the task type (major or minor) would be expected instead of the task name. If you would want it the other way around, you can change
If you would rather have the task name added next to the task type, you could insert the item like so :
so not only does the (highlighted) line have to be included to add the name, but all references to cells E:X should be updated to correspond to F:Y.
The way I looked at the example, the task type (major or minor) would be expected instead of the task name. If you would want it the other way around, you can change
target.Range("B" & taskRow) = taskType
target.Range("B" & taskRow) = taskName
If you would rather have the task name added next to the task type, you could insert the item like so :
[...]
target.Range("G1").Formula = "=F1+7"
target.Range("G2").Formula = "=F2+7"
target.Range("G1:G2").AutoFill target.Range("G1:Y2")
target.Range("F1:Y2").NumberFormat = "d-m-yyyy"
[...]
target.Range("C:Y").ColumnWidth = 12.29
[...]
target.Range("F3:K17").Select
[...]
target.Range("F1") = firstDate
[...]
target.Range("A" & taskRow) = taskID
target.Range("B" & taskRow) = taskType
[b]target.Range("C" & taskRow) = taskName[/b]
target.Range("D" & taskRow) = taskStart
target.Range("E" & taskRow) = taskDue
[...]
For Each Item In target.Range("F2:Y2")
[...]
so not only does the (highlighted) line have to be included to add the name, but all references to cells E:X should be updated to correspond to F:Y.
ASKER
Akoster,
Thank you for the explanation. It's still rather difficult for me to follow but in looking through the code, I think we may have a misunderstanding.
The terms Major and Minor Tasks are simply place holders for Task descriptions. It's the numbering system that is used to determine which are major and which are minor tasks. So for example;
1 may be "Project Manager Role Selection" ("Project Manager Role Selection" would be a major task since 1 is a whole number.
if I then added 1.1 as "Define PM Role" (that would be a minor task under the major)
if I then added 1.2 as "Get list of PM candidates" (that would be a 2nd minor task under the same category)
and so on.
When I put in the data, it should show up from the Input to the Result screen in Columns A and B. Then when the dates are entered, it simply adds the color coding so as to show the timeline.
I do not think we need another tab called Gantt Chart since the chart is represented in the Result Tab.
Make sense?
B.
Thank you for the explanation. It's still rather difficult for me to follow but in looking through the code, I think we may have a misunderstanding.
The terms Major and Minor Tasks are simply place holders for Task descriptions. It's the numbering system that is used to determine which are major and which are minor tasks. So for example;
1 may be "Project Manager Role Selection" ("Project Manager Role Selection" would be a major task since 1 is a whole number.
if I then added 1.1 as "Define PM Role" (that would be a minor task under the major)
if I then added 1.2 as "Get list of PM candidates" (that would be a 2nd minor task under the same category)
and so on.
When I put in the data, it should show up from the Input to the Result screen in Columns A and B. Then when the dates are entered, it simply adds the color coding so as to show the timeline.
I do not think we need another tab called Gantt Chart since the chart is represented in the Result Tab.
Make sense?
B.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Akoster,
Appreciate the hard work on this.... you really hung in there with me on getting this done and done right. I'm going to ask a related question shortly and hope you will pick up on it. Again, appreciate such professionalism.
B.
Appreciate the hard work on this.... you really hung in there with me on getting this done and done right. I'm going to ask a related question shortly and hope you will pick up on it. Again, appreciate such professionalism.
B.