First, I know this is really stretching what Sheets is meant to do, but here we are.

Sheet 1 is a list of meetings and who is assigned to run them (the larger sheet is extrapolated to many meetings across multiples days with many names):

Sheet 2 is who is eligible to be cover the meeting if the assigned employee is out:

Basically, my manager wants a button he can hit to say "Hansen isn't here, who is covering them?"

The following logic would apply to select the correct Eligible Employee:

1. Coverage Count is the lowest/smallest in the range

2. Attendance = Present

3. Day != Exclusion Day

For above, if Hansen needed coverage, the logic would pass Kelley because they can't cover Monday, pass Johnson because they are Absent, and return Ramirez because they have a lower Coverage Count than Callahan.

In addition to returning Ramirez, the macro would also "record" a coverage for Ramirez of 1, so their Coverage Count would now be 1.3. That way if the manager then tried to find coverage for Jones next, Ramirez would not be returned again until everyone other Eligible Employee has covered again.

I'm stumped.

At first I tried using FILTER...

=FILTER(Covering_Emp,Covering_Duty<>"Friday",Dovering_Absence="Present",SMALL(Covering_Count,1))

But

1. I don't think what I need and
2. Throwing "“FILTER has mismatched range sizes”

CodePudding user response：

try:

=BYROW(A2:A, LAMBDA(x, INDEX(IFNA(SORTN(
 FILTER({F2:F, G2:G}, I2:I=x, H2:H="Present"), 1, 1, 2, 1)),,1)))

CodePudding user response：

I think because of the somewhat recursive nature of this question it's easier to try a simple pull-down formula for it in the first place. So any people who have already been used as cover are excluded from the next round of selection using a countif on column D to add one to their coverage count:

=index(sort(filter({F$2:F,G$2:G countif(D$1:D1,F$2:F)},H$2:H="Present",I$2:I<>A2),2,1),1,1)

starting in D2 and pulled down.

You can see that Kelley and Johnson are excluded because they are either absent or not available on Monday.