Assigned Resource

Enoch Mak

Hello

My team utilizes smartsheets to assign resources for project within a set time duration.

I'm looking for the best solution to determine how to view/flag overlapping resource assignments within the same date periods. Is there a way in the drop down menu in contacts to display if said individual(s) are already assigned or NOT assigned to a project?

jmyzk_cloudsmart_jp

Hi @Enoch Mak

Though, not exactly a way in the dropdown menu in contacts to display if the said individual(s) are already assigned or NOT assigned to a project, the "# Tasks in the Same Period" column in the image below alerts the selected dropdown contact to have more than one task.

The formula for the "# Tasks in the Same Period" is

• =COUNT(COLLECT(Task:Task, [Assigned To]:[Assigned To], [Assigned To]@row, Start:Start, >=Start@row, Finish:Finish, <=Finish@row))

The formula for the "Tasks in the Same Period" is

• =JOIN(COLLECT(Task:Task, [Assigned To]:[Assigned To], [Assigned To]@row, Start:Start, >=Start@row, Finish:Finish, <=Finish@row), CHAR(10))

You can check the formula in the following published demo sheet.

https://app.smartsheet.com/b/publish?EQBCT=99eccebfbfd44db5bda0e06799502cb3

Enoch Mak

Hello,

Assigned To has multiple contacts within the cell. It seems like the formula is only reading 1 person in the cell rather than all the individuals assigned to the row.

jmyzk_cloudsmart_jp

Hello @Enoch Mak

In the case of a Multiple Contact List, it is necessary to determine the individual Contacts that make up the list.

Because you can not compare "Task 1 with A, B, and C assigned"and "New Task 1 with A and D assigned", for example.

There is no function to extract individual Contacts from the Multiple Contact List.

Therefore, As in the Multiple Contact List, Contacts are separated by "," we retrieve the Text data corresponding to a Contact using Text-related functions.

Example Contact 1 Contact 2

Using the text data, determine whether the textualized Multiple Contact List contains Contact 1 to Conatct 4, for example. 

• =COUNT(COLLECT(Task:Task, Start:Start, <=Start@row, Finish:Finish, >=Finish@row, [Assigned To Text]:[Assigned To Text], OR(CONTAINS([Contact 1]@row, @cell), CONTAINS([Contact 2]@row, @cell), CONTAINS([Contact 3]@row, @cell), CONTAINS([Contact 4]@row, @cell))))

If so, flag the task as a duplicate task.

The formula to get the text data corresponding to a Contact in the Multiple Contact List is rather tedious, so please refer to the published demo sheet.

https://app.smartsheet.com/b/publish?EQBCT=e27e2089ed8a457aa3a6cf3ef909e10a

I improved the "Task in the Same Period" column to show only the duplicate task using the SUSBSTITUTE function.

Enoch Mak

Hello,

Thank you for this solution.

I'm looking to see if there is a more simplified formula or solution for this unfortunately it'll take a while to set up we have over 100 possible contacts that could be assigned to the specific task. While there could be a max of 10 that could be assigned to one said project. If we added over 100 columns to check for ~100 contacts that'll I'd wager slow down our sheet by a large amount and require a lotta formula set up.

jmyzk_cloudsmart_jp

@Enoch Mak

Method 1 responds to your "added over 100 columns will slow down."

Method 2 answers your "a more simplified formula or solution."

(I have written this comment in a more general form to help other users in the community as well.)

Method 1: Compare duplicate tasks of the same period by cutting out the Contacts of the Multiple Contact List as text.

There is no need to add over 100 columns.

Since the Multiple Contact List has a limit of 20, this method would require a maximum of 40 columns, i.e., 20 columns of C1, C2, etc., to get the "," position that separates Contacts, and 20 columns of Contact 1, Contact 2, etc., to get the "Contact" position.

If, as in your case, the maximum number of users assigned to a project is 10, you would only need 20 additional columns.

With other community users in mind, we have updated the demo sheet to carve out the text value of individual Contacts from the Multiple Contact List up to the current Smartsheet limit of 20.

If you are interested, please request it via this form, and a copy of the file will be shared.

https://app.smartsheet.com/b/form/1d610583385a46e4b5971110b527bc5e

app@cloudsmart.jp is an owner, and you will be the Admin, so please make a copy of the shared sheet and edit it for your use.

There is an article I wrote in Japanese about the limits of Multiple Contact Lists, a translation of which is available, so if a reader of this comment is interested, you can read it at this link

https://cloudsmart.jp/en/smartsheet/tips/contact-list-limit-2/

I recorded a video of how I semi-automated the addition of 40 columns and formulas using various tools.

https://share.descript.com/view/8P8oOXOvoZ6

Note that the logic for comparing the Start and Finish of the task being verified with those of other tasks being compared was incorrect, so I have corrected it.

• Start:Start, <=Finish@row, Finish:Finish, >=Start@row

Method 2 (more simplified formula):  Use the Has function to check whether a candidate Contact to be added as an Assignee has duplicate tasks in the same period.

If the above methods are too complicated, you can select Potential Assignee to display information on the Concurrent Task status created by adding that person as an Assignee of a task. Then, you can use the information to decide whether to add that person. 

To do so, add the following columns and expressions

Potential Assignee: a Single Select Contact List

Concurrent Tasks?

• =IF(HAS(COLLECT([Assigned To]:[Assigned To], Start:Start, <=Finish@row, Finish:Finish, >=Start@row), [Potential Assignee]@row), 1, 0)

Which Task? 

• =JOIN(COLLECT(Task:Task, [Assigned To]:[Assigned To], HAS(@cell, [Potential Assignee]@row), Start:Start, <=Finish@row, Finish:Finish, >= Start@row), CHAR(10))

Comparing the two methods, the former checks for all Assignees of a task being verified to see if they have other tasks in the same period.

In contrast, the latter provides information on overlapping tasks for an additional Assignee candidate.

While the former is better suited for checking the overlap status for the project as a whole, the latter is better suited for checking the overlap status when considering additional Assignees for individual tasks.

In your case, this may be easier and better.

We have updated the Demo Sheet to compare both methods, so please check it out.

New Tasks 1 to 5 are editable, so you can see what duplicate information each method provides.

https://app.smartsheet.com/b/publish?EQBCT=e27e2089ed8a457aa3a6cf3ef909e10a

Also, if you need a copy of this sheet, please use the form below to request one. (This is the same form as the previous one.)

https://app.smartsheet.com/b/form/1d610583385a46e4b5971110b527bc5e