Search code examples
excelexcel-formulaautomation

Automation of Rotation using Excel

A few months ago I had a similar question this and with the help of the forum here I managed to solve my query. Here is the link to my previous question: Automation in Excel for shift patterns

I have taken my shift pattern a step further and again I got stuck slightly. Basically, I have 2 tabs involving the following tables.

Table 1 enter image description here

Table 2:

enter image description here

What I am trying to do is that in Column D of Table 1 I will get the names automatically generated based on the date in Cell C2 and the Name based on the Shift Pattern found in Table 2 based on the date. Currently it is just assigned to 08/11/2021, if the date is changed to 09/11/2021 nothing will change.

Here is the formula I use to get the values in Column D of Table 1.

=INDEX(Roster!$D$21:$D$28,SMALL(IF(Sheet1!C4=Roster!$E$21:$E$28,ROW(Roster!$E$21:$E$28)-ROW(Roster!$E$21)+1),COUNTIF(Sheet1!$C$4:C4,C4)))

Can someone help please?

Thanks

Solution

  • If you have Excel 365, you can put this formula in cell C4 of Table 1:

    =SORT( INDEX( Roster!$E21:$K28,
       SEQUENCE( ROWS(Roster!D21:D28) ),
       MATCH( C3, Roster!E19:K19, 0 ) ) )

    and this formula in D4 of Table 1.

    =SORTBY( Roster!$D$21:$D$28,
         INDEX( Roster!$E21:$K28,
                SEQUENCE( ROWS(Roster!$D$21:$D$28) ),
                MATCH( C3, Roster!$E$19:$K$19, 0 ) ) )