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

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

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)

Forum Discussion

Need help with rostering tool

Resources