LeeHill
Level 1 (4 points)
iWork

Q: Populate a list of names based on "Availability status"

I am having trouble writing a formula to cull names from a list based on their "Availability Status" (Pictures for clarity below).

I know I am missing something ultra simple on this and would love a nudge in the right direction

Let's say I have multiple sheets of Departments we will call them Admins, Tech, and Sales.

(In the Pictures the Departments are tables instead of sheets, with propers syntax I can figure it all out)

 

I need to create an overview page of all the Departments so that the end user can have a quick snapshot of who is available for a task at any given moment.

"Jenny we need a Tech ASAP"

Jenny opening up the overview tab will instantly see her Tech Ted is available.

Now Jenny handles multiple Departments and has a full plate.

Two seconds later she may hear "Jenny we need a salesperson"

BOOM Overview Sheet!

Bob is available!

PHEW!

The day is saved and Jenny marks both Ted and Bob as "Unavailable", resuming her day.

Clearly, Jenny can sort these lists by "Availability Status" but that is 3 too many clicks.

Jenny needs a grand perspective.

 

Before we begin I have also set a formula to return "Available" "Unavailable" as a TRUE FALSE in an effort to make this work.

The aforementioned formula could stay if needed.

 

I have tried IF....  IF(People::Sales::Status="Available",People::Sales::A,"")

IF works, sort of ... IF TRUE returns a blank cell that cannot be pushed to the bottom by sorting.

(Picture 2)

 

I have tried INDEX MATCH .......INDEX(People::Tech::A,MATCH(TRUE,People::Tech::C))

INDEX MATCH works in that it returns the bottom-most cell ad Infinium.

(Picture 3)

 

I have tried LOOKUP ..... LOOKUP("Available",People::Admins::Status,People::Admins::A)

LOOKUP works in the same way as INDEX MATCH as it returns the bottom-most cell ad Infinium.

(Picture 4)

 

 

Considerations:

 

It isn't 3 Departments is it 150 people with 15 different skill sets, we will say 10 per skill set.

Jenny really needs a grand perspective as the requests come in fast and hot.

 

"Available" "Unavailable" is a dropdown which returns a value of TRUE FALSE if needed.

 

Jenny is moderately tech savvy, so if we have to teach her a trick or two the world won't end.

 

I am fair to middling with Numbers and can make it work across the board with the proper syntax given once.

 

In a perfect world, this would be "Column Based" so Jenny can add or remove people at will and not have to readjust the formula.

 

 

I know  I am missing something simple, please kick me in the buns.

 

 

 

Screen Shot 2016-08-24 at 9.46.19 PM.pngScreen Shot 2016-08-24 at 9.59.30 PM.pngScreen Shot 2016-08-24 at 10.08.00 PM.pngScreen Shot 2016-08-24 at 9.59.43 PM.png

VIN, iMac (27-inch Mid 2010), Mac OS X (10.1.x)

Posted on Aug 24, 2016 9:25 PM

Close
LeeHill

Q: Populate a list of names based on "Availability status"

  • All replies
  • Helpful answers

  • by Wayne Contello,

    Wayne Contello Wayne Contello Aug 25, 2016 6:24 AM in response to LeeHill
    Level 6 (19,421 points)
    iWork
    Aug 25, 2016 6:24 AM in response to LeeHill

    I would approach this problem using a two table approach like this:

    Screen Shot 2016-08-25 at 8.18.06 AM.png

     

    combine all the workers into a single table as shown above.

     

    Add an additional column at the end (I titled it "Available Count"... but the name is not important.

     

    D2=IF(C2="Available", B2&COUNTIFS($C$1:C2,"Available", $B$1:B2, B2), "")

     

    this is shorthand for... select cell D2, then type (or copy and paste from here) the formula:

    =IF(C2="Available", B2&COUNTIFS($C$1:C2,"Available", $B$1:B2, B2), "")

     

    select cell D2, copy

    select cells D2 thru the end of column D, paste

     

    Now create a second table (I titled this table "Availability")

    this table has two header rows. 

    Enter the type of worker to check availability for in cell B1 ("Tech" in this example)

    A3=IFERROR(OFFSET(Contractors::$A$1,MATCH($B$1&ROW()−2, Contractors::D,0)−1, 0), "")

    select cell A3, copy

    select cells A3 thru the end of column A, paste

     

    now when you change the role in cell B1 the list will update as you change the details in the table "Contractors"

     

    if you want to see all three simultaneously, duplicate the table "Availability" and change cell B2 to "Admin", then  duplicate again and change B2 to "Sales"

  • by t quinn,

    t quinn t quinn Aug 25, 2016 7:10 AM in response to LeeHill
    Level 5 (5,063 points)
    Mac OS X
    Aug 25, 2016 7:10 AM in response to LeeHill

    Hi Lee,

     

    I want to encourage you to consider Wayne's approach of a single table with all contractors listed. In Numbers it is much easier to extract data from a single table to smaller report tables. This would lead you to a situation where when you want to see all your 'sales' people you would have a report table similar to the sheet you now have taht you are trying to retreive dat from. Another advantage to having all contractors on one table is that you will be able to compare data across departments.

     

    quinn

  • by SGIII,

    SGIII SGIII Aug 26, 2016 7:32 AM in response to LeeHill
    Level 6 (10,796 points)
    Mac OS X
    Aug 26, 2016 7:32 AM in response to LeeHill

    As noted this is easier if you have your data in one table. But if you need to keep it in separate tables (because, for example, each department is responsible for listing availability and you don't want one department mistakenly inadvertently changing the data for another department) you can set things up exactly as in your example, as follows:

     

    Screen Shot 2016-08-26 at 10.21.31 AM.png

     

    You need an index column in each table, with this formula in B2, filled down:

     

       =IF(B2="Available",MAX(C$1:C1)+1,"")

     

    This increments a counter each time it sees an "Available" in column B.

     

    In the Availability Lookup table you can have a Pop-Up Menu in A1 that selects a department.

     

    The formula in A2, filled down, is:

     

       =INDEX(INDIRECT($A$1&"::A"),MATCH(ROW()−1,INDIRECT($A$1&"::C"),0))

     

    The INDIRECT parts of this simply tell the formula which table is the data source based on the choice in A1.

     

    Without the INDIRECT looking up the name from the 'Tech' table would be like this:

     

       =INDEX(Tech::A,MATCH(ROW()−1,Tech::C))

     

    The MATCH looks up the row number the formula is on and looks this up column C in the Tech table, and feeds this to INDEX, which looks up the corresponding value on that row in column A of the Tech table.

     

    SG