Forum Discussion

Tomcomehome's avatar
Tomcomehome
Copper Contributor
Aug 16, 2020

Need help with rostering tool

Hi All

 

I'm making a rostering tool for my business and I have run into a snag. Please see the above picture. I would like to get the name of the person with a number from the right column in the blue cell on the left. So for example, If Tom has 8, Tom's name appears in the blue cell. However, if I remove the 8 for Tom and give them to Juan, then Juan's name appears in the blue cell. And of course if two names are added, it should cancel it out.

 

Can anybody help me with a specific formula do this?

excel

9 Replies

  • mathetes's avatar
    mathetes
    Silver Contributor
    Aug 17, 2020

    Tomcomehome 

     

    If the FILTER function works on your Excel (it's only available in the most recent release of Excel) then this formula should work:

    =FILTER(D1:D7,NOTBLANK(F1:F7))

     

    But that's only an incomplete solution at this point. It's not clear to me what the "8" represents. Will you be using other numbers as well? When? Why?

     

    This could be made more nuanced, I suspect. That doesn't seem to be a complete roster, for example. Are there not more individuals on that AM shift? What about the PM shift? Are you just picking people off randomly, or will there be a patter to the assignments? What does it even mean when you say, "And of course if two names are added, it should cancel it out." What is the first "it," the one that does  the canceling; and then what is the second "it" that gets canceled?

    And so forth.

     

     

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Aug 17, 2020

      mathetes 

      Adding multiple conditions to the formula

      = XLOOKUP( 1, 
    (Table1[Hrs]>0)*(Table1[Shift]="am"), 
     Table1[Employee], 
     "Not assigned"
  )

      (I have also adopted a style which requires input data to be held in Tables)

    • Tomcomehome's avatar
      Tomcomehome
      Copper Contributor
      Aug 17, 2020

      Hi mathetes 

       

      Thank you for taking the time to get back to me.

       

      The 8 represents the working hours. And there is only one staff member on at any time. So only one name should appear in the blue cell. Once I know the correct formula/method, I can apply this to the PM cells as well.

       

      The screenshot I have put up a simplified version of my spreadsheet for clarity.

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Aug 17, 2020

        Tomcomehome Allow me to contribute a few more possible solutions.

        =FILTER(D:D,NOT(ISBLANK(F:F)),"None")

or

=FILTER(D:D,F:F<>"","None")

        I believe this is what mathetes had in mind originally.

         

        Alternatively, if you insist on finding only the number 8 in column F:

        =FILTER(D:D,F:F=8,"None")

         

        or use XLOOKUP (if supported by your Excel version)

        =XLOOKUP(8,F:F,D:D,"None")

         

        or use the more traditional combination of INDEX and MATH or just LOOKUP. These will work in all Excel versions

        =IFERROR(INDEX(D:D,MATCH(8,F:F,0)),"None")

or

=IFERROR(LOOKUP(8,F:F,D:D),"None")

         

Resources