'Assigned To' Formula Quirks/Advice

Steve@VHL

I am building a large Control Center solution for a customer that contains 20+ schedule templates that are being used to manage different portions of the workflow. In the templates, there is a "Department Owner(s)" multi-select column that reflects assignment of the associated department (PM, Editorial, PRD, etc.). Then, the "Assigned To" column contains an INDEX/MATCH formula to pull the person assigned for that department from a separate sheet. I am experiencing some quirky behavior in the "Assigned To" column, and I hoping someone can provide some assistance. Below is an explanation of the issues I am seeing and a couple screenshots to show them.

1. INDEX/MATCH formula to derive 'Assigned To' from 'Department Owner(s)' column results in text value, not Contact List [#1]. The Contact List attribute is important since the customer will be using automations to notify people of overdue Tasks (based on "Assigned To").
   • FIX: Changing the "Assigned To" column to Multiple Contacts changes it to Contact List. However, changing back to Single select followed by Refresh removes Contact List
2. Multiple Dept Owner(s) creates error in Assigned To column [#2]
   1. FIX: Add Secondary Dept Owner column [#3] and modify formula to perform (and join) two INDEX/MATCH calculations [#4]. However, a refresh still removes Contact List attribute [#4] from columns with Multiple Contacts

So, this scenario is close to working properly, but I can't proceed with this solution as is based on this lack of reliability. Does anyone have any suggestions on how to better address this requirement? If needed, I can jump on a video call to show the details on this situation.

NOTE: I have already created a ticket with Smartsheet Support and discussed this with Pro Desk.

Paul Newcome

It sounds to me like you are experiencing expected behavior.

Both the referenced column and the column the INDEX/MATCH formula are going into need to be contact type columns for it to pull through as a usable contact, and there is currently no way to write a formula that will output multiple usable contacts within a single cell.

Paul Newcome

It sounds to me like you are experiencing expected behavior.

Both the referenced column and the column the INDEX/MATCH formula are going into need to be contact type columns for it to pull through as a usable contact, and there is currently no way to write a formula that will output multiple usable contacts within a single cell.

Steve@VHL

@Paul Newcome Thanks for your reply here. The interesting thing about this situation is that I do have a formula that is bringing in two Contact List entries into a single column as shown below. However, it is not being retained after the page is saved/refreshed.

Here is the formula that I am using--basically stringing two INDEX/MATCH formulas together:

And here is the result of that formula showing two Contacts in a single cell (as evidenced by the icons to the left of the email addresses):

After I saved and refreshed the sheet, the icons disappeared and the Contact List functionality is gone.

So, you don't think there is any way to retain the Contact List functionality then? My solution to this would be to add a second 'Assigned To' column to perform the INDEX/MATCH against the 'Secondary Dept Owner' column.

Again, I appreciate your input!

Steve

Paul Newcome

Unfortunately that is actually a known glitch.

Yes. The only option at this point would be to have separate columns for each of the Assigned.