How can I create a drop-down list of available employees to fill a shift between a range of dates?

Question

Given a pool of Employees, previously scheduled shifts, and a new unscheduled shift; can I create a drop-down list that only displays the names of Employees who are not scheduled in a range of dates that overlap with a new proposed shift?

Shifts will always display in the form of a Start Date in one cell, and End Date in another cell.

=IF(COUNTIFS($A$3:$A$5,"<="&E1,$B$3:$B$5,">="&E1)+COUNTIFS($A$3:$A$5,"<="&D1,$B$3:$B$5,">="&D1),"Overlap","Do not overlap")

This formula was useful to highlight potential conflicts between date ranges, but I want the potential formula to action the employee name in the cell from a drop down list of only available employee names!

Is that possible?

Answer 1

Dynamic dropdown list per row, with helper range, without VBA

For data validation lists, we can use only a limited set of functions - TEXTSPLIT etc. to get list from CSV, are not available.

So, here I have used OFFSET ; this is a volatile function and may not be suitable for very large datasets.

Formulas below use two tables, Roster and Employee.

---

1. A couple of helper ranges are used to build the lists; so select an area with
   number of rows in roster X (max number of employees + 2) these could be placed in a separate sheet that's hidden later.
2. In the first range "Roster Index", place indices for rows in the roster. These are referenced in the data validation formula. =SEQUENCE(ROWS(Roster))
3. Build valid entries for these rows - cell holding the formula is named valid_entries and referenced in the data validation.
4. Select cells for validation from table Roster, with first cell highlighted, use the first cell in "Roster Index" for validation formula, as shown below - make sure that the column referece is relative ($B18 and not $B$18): =OFFSET(valid_entries#, $B18 - 1, 0, 1, SUM(--ISTEXT(INDEX(valid_entries#, $B18, 0))))

Get valid entries

=LET(
    has_no_overlaps, LAMBDA(roster_row, for_emp,
        LET(
            for_start, INDEX(Roster[Start], roster_row),
            for_end, INDEX(Roster[End], roster_row),
            roster_index, SEQUENCE(ROWS(Roster)),
            overlapping, (roster_row <> roster_index) * (Roster[Name] = for_emp) *
                (
                    ((for_end > Roster[Start]) * (for_end <= Roster[End])) +
                        ((for_start >= Roster[Start]) * (for_start < Roster[End]))
                ),
            IF(SUM(overlapping), 0, 1)
        )
    ),
    emp_list_no_avail_added, VSTACK(
        Employee,
        TEXTSPLIT("--,Supervisor;--,Worker", ",", ";")
    ),
    name, INDEX(emp_list_no_avail_added, 0, 1),
    pos, INDEX(emp_list_no_avail_added, 0, 2),
    roster_index, SEQUENCE(ROWS(Roster)),
    valid_entries_for_shifts, DROP(
        REDUCE(
            "",
            roster_index,
            LAMBDA(acc, roster_row,
                VSTACK(
                    acc,
                    LET(
                        available_for_pos, FILTER(
                            name,
                            pos = INDEX(Roster[Position], roster_row)
                        ),
                        available_for_roster_row, FILTER(
                            available_for_pos,
                            MAP(
                                available_for_pos,
                                LAMBDA(emp_name,
                                    OR(
                                        emp_name = "--",
                                        has_no_overlaps(roster_row, emp_name)
                                    )
                                )
                            )
                        ),
                        TOROW(available_for_roster_row)
                    )
                )
            )
        ),
        1
    ),
    valid_entries_for_shifts
)

Answer 2

This is just a quick and dirty VBA without any proper error handling. You will need to make the date logic a bit more complex and add some error handling routines but it works OK as a template.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myCollection As Collection
Dim item As Variant

Set myCollection = New Collection

    If Target.Column = 3 Then
    
        Set employeeRange = Range("F2:F" & Cells(Rows.Count, "F").End(xlUp).Row)
        Set rosterRange = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    
        On Error Resume Next ' To skip errors in case of duplicate values
        
        For Each cell In employeeRange
            
            myCollection.Add cell.Value, CStr(cell.Address) ' Add cell value with the cell address as a key
        Next cell
        On Error GoTo 0

        For i = 2 To rosterRange.Rows.Count + 1
            If i <> Target.Row Then
                If (Cells(Target.Row, 1).Value <= Cells(i, 1).Value) Then
                        For j = myCollection.Count To 1 Step -1
                            If myCollection.item(j) = Cells(i, 3) Then
                                myCollection.Remove j
                                Exit For
                            End If
                        Next
                End If
            End If
        Next
        
        
        employeeDropdownList = ""
        For Each employeeName In myCollection
            employeeDropdownList = employeeDropdownList & employeeName & ","
        Next employeeName
        
        If Len(employeeDropdownList) > 0 Then
            employeeDropdownList = Left(employeeDropdownList, Len(employeeDropdownList) - 1)
        End If
        
        With Target.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:=employeeDropdownList
                .IgnoreBlank = True
                .InCellDropdown = True
                .ShowInput = True
                .ShowError = True
        End With
        
    End If
    
    
End Sub

Answer 3

How about this approach:

Create a roster table with Start Date, End Date and Employee Name. Created an Employee List table. Add a new column to your roster table with the following formula:

=LET(
    EmployeeList, EmployeeNames[Employee list],
    RosterStart, [Start Date],
    RosterEnd, [End Date],
    RosterEmployee, [Employee name],
    StartDate, [@[Start Date]],
    EndDate, [@[End Date]],
    AssignedEmployees, FILTER(RosterEmployee, (RosterStart <= EndDate) * (RosterEnd >= StartDate)),
    UnassignedEmployees, FILTER(EmployeeList, ISNA(MATCH(EmployeeList, AssignedEmployees, 0))),
    ARRAYTOTEXT(UnassignedEmployees)
)