Excel for task tracking and workflow

[BravoBravoAu]

Hi all - thanks for your time and expertise.

I'm considering using Excel to update the way my team provides monthly updates to the boss. I imagine having a shared workbook with a worksheet for each month of the year. Each row will refer to a task assigned to the ten staff in my work area and around ten columns containing relevant categories about the task (eg who its assigned to, what has happened, what you're planning next month) and then finally - the important bit - a column for the boss to write comments. The format in each worksheet will be the same, however will clearly be blank in future worksheets until that month rolls around. Preserving the point in time information is important so overwriting of cells (e.g. "progress this month" in column D) will not occur.

The part I'm struggling with is how do I add rigour to the integrity of the entries?! Can I show time/date that the comments are entered by the boss and by each staff member and for that matter, capture the user? In the below, that would be Columns D and F. I suppose I'm thinking similarly to how Word comments and track changes works?!

I'm happy to hear any thoughts about this!! Bright ideas and innovation alike is encouraged.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Task ID[/TD]
[TD]Assigned to[/TD]
[TD]Date assigned[/TD]
[TD]Progress this month[/TD]
[TD]Progress next month[/TD]
[TD]Boss oversight[/TD]
[/TR]
[TR]
[TD]AG1[/TD]
[TD]A.Brown[/TD]
[TD]1 Jan 18[/TD]
[TD]Almost completed. [/TD]
[TD]Review file and finish report[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AG2[/TD]
[TD]A.Brown [/TD]
[TD]12 Apr 18[/TD]
[TD]In abbeyance.[/TD]
[TD]Prioritise task and request information from stakeholders[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BG1[/TD]
[TD]P.Green[/TD]
[TD]30 Jan 18[/TD]
[TD]Requested extensive information about stakeholder activities[/TD]
[TD]Follow up requests and compile when recieved.[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Yongle]

you may find this easier to adapt (same as original , written differently)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 Then Exit Sub   'ignore changes in header row

    If Target.Column = Range("F1").Column Then
        Cells(Target.Row, "G") = Date
        Cells(Target.Row, "H") = Time
        Cells(Target.Row, "I") = Application.UserName
    End If

End Sub

 

[Yongle]

Read post#11 to see answer to post#10

Below VBA provides user with a method to select the rows to which date,time and name is added
Test it first on an empty worksheet to see how it works and then modify to match required columns etc

How it works
- when macro is run an input box appears
- hold down the {control key} and then left click the cells required (any cell in required rows will do)
- single cells, ranges, non-contiguous ranges can be selected with the mouse
- release the {control key} and click OK

Sub Test()

Dim cel As Range, SelectedCells As Range
Set SelectedCells = Application.InputBox("Hold down {CTRL} as you select cells:", Type:=8).Rows

For Each cel In SelectedCells
    With ActiveSheet
        .Cells(cel.Row, "A") = Date
        .Cells(cel.Row, "B") = Time
        .Cells(cel.Row, "C") = Application.UserName
    End With
Next cel

End Sub