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 i...
excel
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")

       

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

        Riny_van_Eekelen 

         

        Indeed, you read my mind on what I had originally intended. I realized I was taking a chance, just responding to an image and not actually testing.  But NOTBLANK really should be a function, don't you think?

Resources