Looping Through Values with Index/Match for schedule creation

[Johncapov]

I'm creating a schedule that loops through a list of approver/preparer names. The goal is to have each individual evenly distributed throughout the list on different days which I have accomplished.

However, the last objective I have is to make it so if the preparer or approver on duty is on vacation that day it will automatically switch to the next available person within the list. Note the preparer can not be the same as the approver.

Is there a way to complete this with a function? I'm very close but cant figure out how to make it so it looks at the 3 different people that are out on vacation and grabs the next person who is available.

Here are the formulas I have so far which rotates the list as needed but does not take into account if the person is out of the office the Vacation validation section was my attempt at seeing whether that person was out of the office, however I believe all of this could be coupled into one function/vba solution?:

Preparer Column: =IF(H3<>"NO","HOLIDAY",IF(OR(WEEKDAY(A3)={1,7}),"WEEKEND",IF(MATCH(F3,B3:D3,0),OFFSET('Input Data (For Schedule Prep)'!$C$2,MOD((ROW(A2)-3),COUNTA('Input Data (For Schedule Prep)'!C:C)-3),0),IF(AND(WEEKDAY(A3)<>{3,5},(K3="Yes")),'Input Data (For Schedule Prep)'!$G$2,OFFSET('Input Data (For Schedule Prep)'!$C$2,MOD((ROW(A2)-1),COUNTA('Input Data (For Schedule Prep)'!C:C)-1),0)))))

Approver Column: =IF(H3<>"NO","HOLIDAY",IF(OR(WEEKDAY(A3)={1,7}),"WEEKEND",IF(G3=D3,OFFSET('Input Data (For Schedule Prep)'!$C$2,MOD((ROW(A1)+1),COUNTA('Input Data (For Schedule Prep)'!C:C)-1),0),OFFSET('Input Data (For Schedule Prep)'!$C$2,MOD((ROW(A1)-2),COUNTA('Input Data (For Schedule Prep)'!C:C)-1),0))))

 

[Johncapov]

Does anyone have a clue?

 

[gaz_chops]

You are far more likely to get a response if instead of posting Pictures, which require someone to recreate all the data manually, you post the sample data, this allows it to be copied and pasted and worked on. Use the XL2BB to post the data sample.

 

[Johncapov]

Okay, my apologies I'm new to this forum. I think I found a alternative just involves looping through a range.

Essentially I would like to keep adding 1 to each value in column F until the value to the left in Column E = 0. Then go to the next in the column until all of the populated values = 0. In column E and D there is a formula that references that and looks to see if that person is Out Of the Office. Would this be a vba solution? The code below is a starting point but doesn't skip to each row in column F.

Sub FillDown()
Dim xRng As Range
Dim xRows As Long, xCols As Long
Dim xRow As Integer, xCol As Integer
Set xRng = Selection
xCols = xRng.Columns.CountLarge
xRows = xRng.Rows.CountLarge
For xCol = 1 To xCols
For xRow = 1 To xRows - 1
If xRng.Cells(xRow, xCol) <> "" Then
xRng.Cells(xRow, xCol) = xRng.Cells(xRow, xCol).Value
If xRng.Cells(xRow + 1, xCol) = "" Then
xRng.Cells(xRow + 1, xCol) = xRng.Cells(xRow, xCol).Value
End If
End If
Next xRow
Next xCol
End Sub