Need help with rostering tool

[Tomcomehome]

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?

[#2]

@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.

[#3]

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.

[#4]

@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")

[#5]

@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)

[#6]

@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?

[#7]

@mathetes Indeed! Seems a bit odds that one has to wrap ISBLANK in a NOT statement to test for  "NOT BLANK". A function called ISNONBLANK would be consistent with ISNONTEXT vs. ISTEXT. Perhaps there is a deeper logic to not having such a function. But, to be honest, I never really gave it much thought. :-))

[#8]

@Riny_van_Eekelen 

Deeper logic?  I don't think so!

We are talking about a system designed from the ground up to meet the needs of users that do not apply logic; nor do they wish to.  There are points at which mathematical rigour enters the equation (e.g. dynamic arrays) but in the main the answer seems no deeper than 'it seemed a good idea at the time'.

In the present example, once one realises that the field being tested contains time durations, the desired test

= IF( ISNONBLANK(duration), ... )

can be reversed to become

= IF( ISNUMBER(duration), ... )

or even

= IF( duration>0, ... )

The test that I still tend to establish by trial and error is one that picks up a blank cell or a zero length string returned by a formula.

= IF( LEN(duration)>0, ... )

appears somewhat clumsy. 

[#9]

@Peter Bartholomew 

Deeper logic? I don't think so!

We are talking about a system designed from the ground up to meet the needs of users that do not apply logic; nor do they wish to. There are points at which mathematical rigour enters the equation (e.g. dynamic arrays) but in the main the answer seems no deeper than 'it seemed a good idea at the time'.

Surely you're not saying "the emperor has no clothes"?!

In the present example, once one realizes that the field being tested contains time durations,...

Part of the difficulty here (speaking of deeper illogic) is that the field being tested is being used for dual purposes--to indicate duration, but ALSO to simply identify a row--and asking a field to perform dual purposes can come back to haunt us.

That's partly why I wanted to see the bigger picture, how this "rostering system" was being designed in the full.

[#10]

@mathetes 

I would suggest that the emperor appears to have a peculiarly varied wardrobe.  It is just unfortunate that it is the most inadequate elements of the wardrobe that feature to greatest extent when it comes to popular acclaim.

I fully agree with you that greater oversight of the problem is needed if we are not to lead the OP into dead ends.