I have the following formula in the 'Available Staff' sheet:
=LET(AvailabilityCalc,FILTER(AllStaffProjectAllocationTbl, IF(ISBLANK($I$11),1,(AllStaffProjectAllocationTbl[Role]=$I$11))*IF(ISBLANK($I$8),1,(AllStaffProjectAllocationTbl[Employee]=$I$8))*IF(ISBLANK($I$14),1,(AllStaffProjectAllocationTbl[Discipline]=$I$14))* (AllStaffProjectAllocationTbl[End Date]<$I$2)*(TRANSPOSE(MMULT(SEQUENCE(1,ROWS(AllStaffProjectAllocationTbl),1,0),TRANSPOSE(AllStaffProjectAllocationTbl[End Date]>TRANSPOSE(AllStaffProjectAllocationTbl[End Date]))*(AllStaffProjectAllocationTbl[Employee]=TRANSPOSE(AllStaffProjectAllocationTbl[Employee]))*(1)))-MMULT((TRANSPOSE(AllStaffProjectAllocationTbl[Employee])=AllStaffProjectAllocationTbl[Employee])+0,SEQUENCE(ROWS(AllStaffProjectAllocationTbl),1,1,0))+1=0)),IFERROR(SORT(INDEX(AvailabilityCalc,SEQUENCE(ROWS(AvailabilityCalc)),{1,4,5,9}),4),""))
that sits in A2 and pulls in all staff that are available after a specific date defined in I2. This unfortunately has an issue as it pulls the data from the staff that have been allocated to a project previously from a 'Staff Project Allocation' sheet and doesn't incorporate the staff from the 'Staff Details' sheet that are yet to be allocated to a project.
The 'Staff Project Allocation' sheet :
The 'Staff Details' sheet :
Someone kindly helped me with the above function and I'm not sure how to modify it to do what I want. Could it be simpler to keep the above as is and create a new set of data to the right of it say, that pulls in the entries from the 'Staff Details' sheet that don't appear in the data created by the formula and are available from the date specified - whilst also being a development role, which I can do by checking their discipline against another table? Or is there a way to modifiy the formula above to incoporate the data? Something I also need to incorporate in the above function, which I overlooked originally, is to exclude any staff member that has had their employment terminated as noted in the Employment Status columns, so any suggestions on how to incoporate this too would be greatly appreciated.
I'm also starting to wonder whether a VBA solution maywell be a better approach, thoughts?
Edit 1
Adjusted function based on the suggestions from Max below. This now includes the ability to filter based on the employment status.
=LET(
staff, AllStaffProjectAllocationTbl,
employee, AllStaffProjectAllocationTbl[Employee],
role, AllStaffProjectAllocationTbl[Role],
discipline, AllStaffProjectAllocationTbl[Discipline],
endDate, AllStaffProjectAllocationTbl[End Date],
employmentStatus, AllStaffProjectAllocationTbl[Employment Status],
rowCt, ROWS(AllStaffProjectAllocationTbl),
roleSelection, $I$11,
employeeSelection, $I$8,
disciplineSelection, $I$14,
availableFromDate, $I$2,
empStatusSelection,"Employment Terminated",
mmult_1, MMULT(SEQUENCE(1,rowCt,1,0),TRANSPOSE(endDate>TRANSPOSE(endDate))*(employee=TRANSPOSE(employee))*(1)),
mmult_2, MMULT((TRANSPOSE(employee)= employee)+0,SEQUENCE(rowCt,1,1,0)),
roleCondition, IF(ISBLANK(roleSelection),1,(role=roleSelection)),
employeeCondition, IF(ISBLANK(employeeSelection),1,( employee=employeeSelection)),
disciplineCondition, IF(ISBLANK(disciplineSelection),1,(discipline=disciplineSelection)),
empStatusCondition, NOT(employmentStatus=empStatusSelection),
AvailabilityCalc,FILTER(staff,roleCondition*employeeCondition*disciplineCondition*empStatusCondition*(endDate<availableFromDate)*(TRANSPOSE(mmult_1)-mmult_2+1=0)),
IFERROR(SORT(INDEX(AvailabilityCalc,SEQUENCE(ROWS(AvailabilityCalc)),{1,4,5,10}),4),"")
)
Edit 2
I now have two tables, one from the Staff Details sheet, showing all staff who are development staff and still employed, this can be seen on the left in the following image (note this all staff allocated to a project or not) and defined by =FILTER(FILTER(StaffDetailsTbl,(StaffDetailsTbl[Employment Status]<>"Employment Terminated")*(StaffDetailsTbl[Dev Role]="Yes")),{1,1,1,0,0,0,1,0,0}), and a separate table defined by the Let function above in edit 1, showing all staff allocated to a project that are available based on a specified date, see table on the right.
I'd now like to merge the table on the left into the table on the right whilst also removing any duplicate entries. Would the most logical first step to be to adjust the filter for the left table to exclude entries that are in the right table? Then look to merge the two in to the right table somehow? Or is it possible to take the Filter defining the left most table and work it directly in to the function defining the right table?
Edit 3 The following function now filters out the entries from the StaffDetailsTbl Table that don't exist in the AllStaffProjectAllocationTbl Table:
=FILTER(FILTER(StaffDetailsTbl,(StaffDetailsTbl[Employment Status]<>"Employment Terminated")*(StaffDetailsTbl[Dev Role]="Yes")*(IF(ISERROR(VLOOKUP(StaffDetailsTbl[Employee], AllStaffProjectAllocationTbl, 1, FALSE)),1,0 ))),{1,1,1,0,0,0,1,0,0})
Giving the resulting table on the left for staff not allocated to a project and those allocated to a project and who are available after a specified date on the right:
I now need to figure out how to merge the filter formula from the left table in to the Let function above, or alternatively, build another table from the two that supports all the roleSelection, employeeSelection, disciplineSelection and availableFromDate functionality. Any thoughts on the best way I can do this please?