Rotating Cell Contents with Exceptions?

[sclipary3]

I would like to use the formula described in the Excelisfun YouTube video

to rotate the content of cells.
=INDEX($A$2:$A$10,IF(ROWS(C$2:C2)>I$9,ROWS(C$2:C2)-I$9,I$5+ROWS(C$2:C2)-1))
My goal is to rotate a list of employees through office tasks. Being an Excel novice, I can follow the logic in the formula, but don't think I have the knowledge to further manipulate or expand on it. My question is, do we think it would be possible to build-in an exception for the event when the next person up in the rotation is not available? I don't want that person to be completely skipped and rotated back to the bottom, but rather have them retain the top spot (for when the are next available). In this case, the second person in line would take the assignment that day, and each of the cells from then second person down in the list would rotate. I completely understand this may take more than one formula to accomplish and I'm open to any/all suggestions for how this might be done.

Thank you in advance.

 

[lrobbo314]

Using VBA

Book1 (version 1) (version 1).xlsb
	A	B	C
1	Name	Available	Task
2	Jason	FALSE	Task A
3	Teresa	TRUE	Task B
4	Eddy	FALSE	Task C
5	Mike	TRUE	Task D
6	Allison	TRUE	Task E
7	Bill	TRUE	Task F
8	Chris	TRUE	Task G
9	Adam	TRUE	Task H
10	Gina	TRUE	Task I
11	Bryan	TRUE	Task J
Sheet8

Sub ROTATE()
Application.ScreenUpdating = False
Dim r As Range:         Set r = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant:    AR = r.Value2
Dim AL As Object:       Set AL = CreateObject("System.Collections.ArrayList")
Dim tmp As String

For i = 1 To UBound(AR)
    If AR(i, 2) Then AL.Add AR(i, 1): AR(i, 1) = vbNullString
Next i

tmp = AL(AL.Count - 1)
AL.removeat (AL.Count - 1)
AL.Insert 0, tmp

For j = 1 To UBound(AR)
    If AR(j, 1) = vbNullString Then AR(j, 1) = AL(0): AL.removeat (0)
Next j

r.Value2 = AR
Application.ScreenUpdating = True
End Sub

 

[katsdelite]

@lrobbo314 I have been trying to do something similar and was hitting wall after wall - this worked like a charm!!​