How to create an automated update request for assigned tasks based on job roll

aweber

Sorry if this question has already been answered but I am having a hard time clarifying what I am looking for to search.

I have a sheet of employees with a helper column (called structure) that is displays their job title as a number using descendants (lower the number the higher their position). If needed this can be reworked to have the lowest ranking employees have the lower numbers.

I have another sheet that is the master schedule where employees will be assigned tasks. I have columns for completion, who is assigned (multi-person select contact column), how long the task took and a column for comments on why something took longer than expected if required.

Multiple employees will be assigned to a single task each day/week and I want to send an update request only to the highest ranking employee assigned. This way I don't have other employees overwriting each others entries. Also the higher ranking employees have a better idea of everything that goes into a task and can give a more in-depth update. I think its the multi-sheet logic that's making this hard for me.

I have a work around where another column is created and whoever assigns the task will put the highest ranking person in as the contact person manually. But I can already see my team assigning tasks and not using the contact person column.

Is there a way to send an automated update request only to the highest ranking employee assigned based on their role in the employee sheet to the task assigned in the schedule sheet?

Michelle Choate 2

This question has been asked a few times - currently there is not a way to select a single person out of a multi-select column to send an automation to. With an automation, it is either sending to a pre-determined specific person, or sending to everyone that is inside of a contact cell.

aweber

I see. I wonder if I added another column called "Contact Person" I could create a formula that could select the highest ranking person from the cell containing assignees and then use that cell to send out an update request?

I am still a beginner with formulas, Can you use a formula to look up all the people listed in a contact column in my employee info sheet, then display the person with the lowest number of descendants using SMALL?

I'm thinking that its doable using formulas but I might have to get creative on how I select assigned people if contact columns cant be used. Maybe I change the "Assigned" column to a text or dropdown column with name selections. Then I could use those values to search my other sheet and return the employee with the highest rank as a row location. Then display the contact column value from that row. Anyone have any ideas on how/if this can be done?

Michelle Choate 2

You can leave everything as contact columns. I think we might be able to solve this with 1 additional column. Now - I am not certain of the formulas needed but I will give it a shot.

You would want to create a RANK column with a formula of something like

=MIN(COLLECT({Rank}, {Employee Names from Employee Sheet}, HAS([Project Sheet Names Column]@row, @cell)))

Then your contact person column would use and INDEX/MATCH formula based on the rank produced in the Rank column.

aweber

Sounds like this would work. I've been playing with it all morning and I can't get it to work. I think one of the issues is that I have multiple employees with the same rank. So using the method outlined above I can only get it to display the first person of that rank. I am attempting to use the following to collect the name of the person based on rank and the rank be the minimum value for the people in the assigned column but I cant get it to work:

=INDEX(COLLECT(Name:Name, Rank:Rank, MIN(COLLECT(Rank:Rank, Name:Name, Assigned@row))))

In my understanding the second collect should be taking the people listed in the assigned column and displaying their ranks. Then the first collect should be using the lowest value in that list and displaying the person with that rank. Can't tell if the issue us using another range as the criterion in the second collect or if the issue is having multiple people with the same rank number.