Task List Scheduler

**excelhelpseeker** · 11-11-2017, 01:30 PM

I Have a task list for Team members on daily basis..(see sheet 2 of sample attached)

In sheet1, Team member are allowed to use only column A for entering their respective Emp Id only

For example, emp 101 enter his id in A1, then data in B1 to be populated automatically (considering his first task)

same way, all TM enter their id in column A, gets their task populated....
End of the all total Task of a TM, "No More Task" to be populated (See B11 of sheet1)

Like wise in C column, Reviewer name to be populated except the doer of the task.
For example, of emp 101 is the doer then reviewer should be any other than 101.

Pls help with formula or macro.

**Kaper** · 11-12-2017, 03:12 AM

May be formulas will be enough?
B2:

Formula:

=IFERROR(INDEX(Sheet2!$A$2:$F$4,COUNTIF(A$2:A2,A2),MATCH(A2,Sheet2!$A$1:$F$1,0)),"No more tasks")

C2:

Formula:

=INDEX(Sheet2!$A$1:$F$1,1,MOD(MATCH(A2,Sheet2!$A$1:$F$1,0)+RANDBETWEEN(0,4),6)+1)

I left these in row 2, you can copy them down just to test.

If not (as you posted a thread in VBA subforum), you can still use these formulas in a code.

In sheet code (right click on tab with sheet name, and use Display Code):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Not Intersect(Target, Columns("A")) Is Nothing Then
  For Each cell In Intersect(Target, Columns("A"))
    If cell.Row >= 2 Then
      cell.Offset(0, 1).Resize(1, 2).ClearContents
      If cell.Value <> "" Then
        cell.Offset(0, 1).FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet2!R2C1:R4C6,COUNTIF(R2C[-1]:RC[-1],RC[-1]),MATCH(RC[-1],Sheet2!R1C1:R1C6,0)),""No more tasks"")"
        cell.Offset(0, 2).FormulaR1C1 = _
        "=INDEX(Sheet2!R1C1:R1C6,1,MOD(MATCH(RC[-2],Sheet2!R1C1:R1C6,0)+RANDBETWEEN(0,4),6)+1)"
        With cell.Offset(0, 1).Resize(1, 2)
          .Calculate
          .Value = .Value
        End With
      End If
    End If
  Next cell
End If
End Sub

Note that because the above code changes only columns B and C no Application.eventsenabled=false at begining and =true at end of the code is needed.