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
Bright01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Arno KosterCommented:
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 ?
0
Bright01Author Commented:
Akoster,

Thanks for jumping in.

Automatically would be preferred since it is really an output graphic.

Thanks,

B.
0
Arno KosterCommented:
This would do the trick :

autmatically processing on data entry
Private Sub Worksheet_Change(ByVal target As Range)
    process
End Sub

Open in new window


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

Open in new window


as in







RASCI-Gantt-Chart-v1.xlsm
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Arno KosterCommented:
whoops,

line 30 should be

target.Name = targetName
0
Bright01Author Commented:
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.
0
Arno KosterCommented:
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.
0
Bright01Author Commented:
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 = False
    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.
0
Arno KosterCommented:
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 :
Const targetName = "Result"

Open in new window


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").Delete

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

Open in new window


0
Arno KosterCommented:
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

target.Range("B" & taskRow) = taskType

Open in new window

to
target.Range("B" & taskRow) = taskName

Open in new window


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")
[...]

Open in new window


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.
0
Bright01Author Commented:
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.
0
Arno KosterCommented:
the misunderstanding likely was caused by the suplpied gantt chart worksheet which only mentioned the task type descriptions. I kept it in because it enables you to check the code results versus the expected results. Indeed the numbering system is used to determine major and minor tasks.
When you put in the data, it will show up in the result worksheet in columns A and B

currently, this version only colors R lines with dates in a workweek. If you want the coloring also to work when weekends are entered, please let me know.

The name of the result page can be changed to whatever you like by updating the 'const targetname' line.

RASCI-Gantt-Chart-v2.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.