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

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

      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. :-))

       

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

        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. 

         

         

Resources