Next cell value

[pawel.b]

Hi! My first post here, so I'd like to thank you all for that incredible work you're doing here! I learned some useful stuff here, so decided to create an account and describe my Excel issue.

Here's the thing: I have a FILTER formula, filtering people that will be available and present at work each day. I want to create a formula taking the next value from the list, if that person is not available - so basically, if that person is out of office, the next person in line should take over his tasks.

- BM A is the task name
- DA is the person assigned to this task, and below you can see he's available, so it's fine
- AV is not available in column 7 & 8, so I need to assign the next person from that FILTER list above


Any chance to help me with the formula?
Thanks in advance,

Pawel

 

[p45cal]

Could you attach a workbook with just the necessary in it to deal with this question?

 

[pawel.b]

Hello p45cal, sure! Let me know if there are any questions.

 

[p45cal]

What would you expect/want the result to be in cell I39?

 

[pawel.b]

Would be good to have one random person from that filtered column in I20#

 

[p45cal]

In the attached there is a formula in cell C39 which doesn't use your C20# formula but incorporates it, so you don't need to see that grid (I've greyed it out).
The reason is that I've tried to put a formula in cell C43 which takes into account that whoever was chosen in cell C39 is now no longer available, so I can't use the same C20# result in the formula.
You will realise, because of the random aspect of the formulae, that the results will change each time the sheet is recalculated.
If you have more tasks below row 43 you'll need to take account of who's already been assigned to the tasks above (cells C39 and C43) in a similar way.
I've also greyed out row 42 because it's not reliable; it doesn't take into account who's been chosen in the tasks above. Instead, the formulae in rows 39 and 43 show the same preferred person if they're available or a different person chosen randomly, if not. I wouldn't bother with rows 38 and 42 at all.
Formula in cell 39:

=LET(a,FILTER($B$3:$B$18,C$3:C$18="Y"),b,C37,IF(ISNUMBER(MATCH(b,a,0)),b,INDEX(a,RANDBETWEEN(1,COUNTA(a)))))

and in cell C43:

=LET(c,$B$3:$B$18,a,FILTER(c,(C$3:C$18="Y")*(c<>C39)),b,C41,IF(ISNUMBER(MATCH(b,a,0)),b,INDEX(a,RANDBETWEEN(1,COUNTA(a)))))

both can be copied across.
I've added conditional formatting to indicate where the person chosen is not the preferred person.

It's not very clever as it is, because a preferred person in a later task might not be available for that task because he's already been chosen at randon for an earlier task. To do this properly requires knowing all preferred people for all tasks beforehand.

 

[pawel.b]

Hello @p45cal, thanks a lot! I know it's a little bit random, but it will help me to create the work schedule (I thought I will just copy and paste as values to prevent it from refreshing). Could you please explain what's a, b and c in these formulas?

I will play around with this as indeed, there are two more tasks to be assigned, but it's a nice start.

 

[p45cal]

explain what's a, b and c in these formulas

Easy:
In

=LET(a,FILTER($B$3:$B$18,C$3:C$18="Y"),b,C37,IF(ISNUMBER(MATCH(b,a,0)),b,INDEX(a,RANDBETWEEN(1,COUNTA(a)))))

a is FILTER($B$3:$B$18,C$3:C$18="Y")
b is C37

In

=LET(c,$B$3:$B$18,a,FILTER(c,(C$3:C$18="Y")*(c<>C39)),b,C41,IF(ISNUMBER(MATCH(b,a,0)),b,INDEX(a,RANDBETWEEN(1,COUNTA(a)))))

c is $B$3:$B$18
a is FILTER(c,(C$3:C$18="Y")*(c<>C39))
b is C41

 

[pawel.b]

I didn't know LET formula, but I get it now. I managed to add two more tasks and it seems to be working fine. Just one more tweak here - do you think the formula below is enough to have the same person for BM A task for two days?

=IF(INDEX(J3:J18,MATCH(I21,B3:B18))="Y",I21,LET(a,FILTER($B$3:$B$18,J$3:J$18="Y"),b,J20,IF(ISNUMBER(MATCH(b,a,0)),b,INDEX(a,RANDBETWEEN(1,COUNTA(a))))))

I will also try to find a way not to assign the same person in next few days but that's a really nice start, thank you so much!

 

[p45cal]

It's addling my brain to try and work it out! I don't know!
However, it might work for the first task, but it'll get awful complicated if you want to do the same for tasks lower down.
If this is going to be an interactive thing, having seen RK appear in cell I21, change I20 to RK (then I21 should appear in I21 always), then change J20 to RK as well?

 

[pawel.b]

Will work it out, thank you for all your help and time spent on this, appreciate that!