Formula to search across multiple contact column

Hi, we are using the following formula, but it only works for calls with a single contact per cell, even though is looking into multiple contacts per cell. We managed to fix this by using a helper column that's the same as the contact column but set as text property. Is there a way to make the formula work with the existing column and avoid using a helper column?

=IF(HAS({Initiative Plan | Assigned To}, [Smartsheet Contact]@row ), IF(AND([Summary Helper]@row = 0, [Smartsheet Contact]@row <> ""), MIN(COLLECT({Initiative Plan | Start Date}, {Initiative Plan | Assigned To}, [Smartsheet Contact]@row ))), " ")

Find more posts tagged with

Formulas

contact smartsheet

Accepted answers

Paul Newcome

Try adding a HAS to the MIN/COLLECT.

MIN(COLLECT({Initiative Plan | Start Date}, {Initiative Plan | Assigned To}, HAS(@cell, [Smartsheet Contact]@row)))

All comments

Paul Newcome

What exactly is the formula supposed to be doing?

Alexandra P

We have a plan sheet that lists tasks, start and end dates, and the people assigned to each task in a multiple contact column. There is also a separate roster sheet with the list of resources, where the start and end dates are calculated using a formula based on the tasks assigned to each person in the plan sheet. We've noticed that the formula only considers tasks with a single resource assigned, and does not account for tasks with multiple resources.

Paul Newcome

Try adding a HAS to the MIN/COLLECT.

MIN(COLLECT({Initiative Plan | Start Date}, {Initiative Plan | Assigned To}, HAS(@cell, [Smartsheet Contact]@row)))

Alexandra P

Thank you! the formula is working

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.