Workflow Automation - Assign People Function.

I have created a Shift Rota for my team. The left axis displays the day and date, the top axis displays each person broken down by team. In the table each person on each day gets assigned a letter depending on the shift they are assigned to (e.g D = Day Shift, A = Afternoon Shift).

I have then set up an automation to collect each person for each shift type per day.

The automations are as follows.

Depending on what letter they have assigned that day, this feeds into another table where it collects the different people on each shift.

The automation works when you reassign someone's shift. E.g you assign me from D (day shift) to N (Night shift) on the shift rota, the automation then puts my contact info into Night Shift Column on table on the right. However, my name still stays in the Day shift column.

Is there a way to remove a name from the previous cell it was in?

I understand using a and index(collect( or Join(collect is probably better but I have not found a way to do this given the many different variables it would have to look up.

Many thanks in advance!

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭
    Answer ✓

    When I create a similar formula, and none of the IF's is true (none of the columns has a "D" in it,) a "0" is placed in the cell. That's unexpected!

    But, unlike what I see in yours, the "0" goes away when at least one of the IFs turns to true.

    When I add a negative condition of "" (a blank value) to one of the IFs, I no longer get a "0" when none of the conditions are true. So add that at the end of your first IF (as shown below) and see if that works to get rid of the 0.

    =IF([email protected] = "D", $Employee$1, "")


    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages