Issues with scaling a work roster schedule
Using a leave register I am trying to automate an AM/PM work roster.
The Leave Register counts blank cells to confirm who's available, & I'm filtering a randomised list of available staff in a separate column (N:Q in image 2):
=LET(
P1AM, FILTER('Leave Register 2026'!A92:A98, 'Leave Register 2026'!C92:C98 = ""),
SORTBY(P1AM, RANDARRAY(ROWS(P1AM)))
)
I'm using variations of the formula below in C11:C28 & D11:D28:
=LET(
P1AM, N4#,
UsedNames, VSTACK(range to check to avoid duplication),
AvailableNames, FILTER(P1AM, ISNA(XMATCH(P1AM, UsedNames)), ""),
IF(ROWS(AvailableNames)=0, "", INDEX(AvailableNames, 1))
)
The Sales cells (Rows 4 & 7) are manually entered by the user at the start of the week, so each cell below this checks these before populating a name, along with any cells populated prior to the specific cell in question. As each cell has a different range it's checking, this seems hard to grow without manual entry across a week let alone at an annual scale.
[link] [comments]
Want to read more?
Check out the full article on the original site