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]

Happy to use VBA?
You could use an event macro to add date and time. Try this:

1. right-click on sheet tab \ select View Code \ paste code below into window on right

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 6 Then
        Target.Offset(, 1).Resize(, 2) = Array(Date, Time)
    End If
End Sub

2. {ALT}{F11} takes you back to Excel


3. Now type anything anywhere in column F (column 6)

Date and time appear in columns G & H
(you may need to widen both columns)
Save the workbook as macro enabled

 

[BravoBravoAu]

Thanks @Yongle - this seems like a neat solution. I can replicate the same in the other column (reflecting the change in column number based on the reqyuired date and time cell).

Any ideas on attributing the change to a user?


****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: cms_table_grid"]
<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="bgcolor: #FAFAFA"]AG1[/TD]
[TD="bgcolor: #FAFAFA"]A.Brown[/TD]
[TD="bgcolor: #FAFAFA"]1 Jan 18[/TD]
[TD="bgcolor: #FAFAFA"]Almost completed.[/TD]
[TD="bgcolor: #FAFAFA"]Review file and finish report[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[Yongle]

Is this what you mean?
- user name added

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row = 1 Then Exit Sub   'ignore changes in header row

    If Target.Column = 6 Then
        Target.Offset(, 1).Resize(, 3) = Array(Date, Time, Application.UserName)
    End If
End Sub

 

[BravoBravoAu]

AWESOME mate - perfect, thank you!!

 

[BravoBravoAu]

@Yongle - if I wanted to assign this macro to a button (shape) and it to only apply the Date, Time, Application.UserName to selected cells, how could I do that?

Below is columns A,H-K

[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]Task ID[/TD]
[TD]Progress this month[/TD]
[TD]Progress [date][/TD]
[TD]Progress [time][/TD]
[TD]Progress [user][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]AG1[/TD]
[TD]Almost complete.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]AG2[/TD]
[TD]In abbeyance.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]BG1[/TD]
[TD]Requested extensive information about stakeholder activities.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The example of what I'm after, is if I wanted to apply the Date, Time, Application.UserName for only rows 2 and 4, I'm looking for a solution where I:

• select those two cells where progress is recorded (H2 and H4)
• press the macro button (in the header of H1 for instance)
• and as a result, apply the Date, Time, Application.UserName in cells I2:K2 and I4:K4

Appreciate any help you can provide!!

 

[Yongle]

Would it not be easier for the user to simply double click in he row after details added?

- insert code in sheet module as before
- to test double-click any cell in I,J or K (or beyond)
- example control included that there must be a value in columns A and I

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column < 9 Then Exit Sub

    If Range("A" & Target.Row) > "" And Range("H" & Target.Row) > "" Then Range("I" & Target.Row).Resize(, 3) = Array(Date, Time, Application.UserName)
    Cancel = True
    
End Sub

 

[Yongle]

Your idea of a button to add details all at once but only to some rows
- which general rule do give VBA so that some rows are excluded?
- my concern is that free text is difficult to control

Is "in abbeyance" the only condition for exclusion?

 

[BravoBravoAu]

Thanks again @Yongle, your first solution is still workable, but I'm exploring if this may be more suitable.

I'm not considering exlusion, rather inclusion by selecting the relevant cell. If there was progress recorded in cells H2 and H4 (as shown above) and I selected then with a left mouse click, then pressed the macro button, that would trigger the Date, Time, Application.UserName in I2-K2 and I4-K4. Does that help?

 

[BravoBravoAu]

Alternatively, using the code you suggested (below), if I was to use this from cells I, P and T (Date, Time, Application.UserName to I=J-L, P=Q-S, T=U-W), what changes would I need to make?

 [COLOR=#333333]Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR]
If Target.Row = 1 Then Exit Sub   'ignore changes in header row

    If Target.Column = 6 Then
        Target.Offset(, 1).Resize(, 3) = Array(Date, Time, Application.UserName)

    End If 

[COLOR=#333333]End Sub[/COLOR]