Assigned Resource

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.

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.

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

@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.

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

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

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.

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