Forum Discussion
Divide list of volunteers into vaccination shifts
Totally forgot to plug that piece in! Made the filter a bit more complicated, but using this worked instead. Basically just looks at which day of the schedule it is, does an xlookup to find which column to filter by (by day), turns whichever cell the day (1-7) is in, into a column reference E:E, then filters that for "Yes".
+TRANSPOSE(IF(Q5="Morning",FILTER(A:A,(B:B="yes")*(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(XLOOKUP(R5,$E$2:$K$2,$E$2:$K$2)),4),"1","")&":"&SUBSTITUTE(ADDRESS(1,COLUMN(XLOOKUP(R5,$E$2:$K$2,$E$2:$K$2)),4),"1",""))="Yes")),FILTER(A:A,(C:C="Yes")*(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(XLOOKUP(R5,$E$2:$K$2,$E$2:$K$2)),4),"1","")&":"&SUBSTITUTE(ADDRESS(1,COLUMN(XLOOKUP(R5,$E$2:$K$2,$E$2:$K$2)),4),"1",""))="Yes"))))Take a look now, runs a little slower but it works, I would only have the random numbers generate once when needed rather than on cell updates.
thank You very much, it looks really smart and simple. The volunteer setup table is all right in your form. I understand, that the FILTER returns an array of volunteers, RANDBETWEEN generates a pointer to this list and XLOOKUP finds the name for the pointer.
The grey column Day is probably prepared to crosscheck the weekday availability of the volunteer, how would you check, if this vac centre weekday matches with the volunteer weekday requirement? (the FILTER function checks now only morning/afternoon shift).
Thank You.
Totally forgot to plug that piece in! Made the filter a bit more complicated, but using this worked instead. Basically just looks at which day of the schedule it is, does an xlookup to find which column to filter by (by day), turns whichever cell the day (1-7) is in, into a column reference E:E, then filters that for "Yes".
+TRANSPOSE(IF(Q5="Morning",FILTER(A:A,(B:B="yes")*(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(XLOOKUP(R5,$E$2:$K$2,$E$2:$K$2)),4),"1","")&":"&SUBSTITUTE(ADDRESS(1,COLUMN(XLOOKUP(R5,$E$2:$K$2,$E$2:$K$2)),4),"1",""))="Yes")),FILTER(A:A,(C:C="Yes")*(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(XLOOKUP(R5,$E$2:$K$2,$E$2:$K$2)),4),"1","")&":"&SUBSTITUTE(ADDRESS(1,COLUMN(XLOOKUP(R5,$E$2:$K$2,$E$2:$K$2)),4),"1",""))="Yes"))))
Take a look now, runs a little slower but it works, I would only have the random numbers generate once when needed rather than on cell updates.
thank You for the mega formula, it is a very good trick, how to reference the weekday from one table to another. And I have learned one more thing - the XLOOKUP returns internally a range (I could not understand the integer output of the XLOOKUP used in the COLUMN formula ...
... but the "Evaluate formula" function shows it
Zdenek