Best Of

I've seen a lot of posts with people trying to figure out how to parse out predecessors so that they can set the status of the next task to "Ready" or something similar. I came up with a solution that worked for me, I figured I'd share it. This solution takes care of the various dependency types and lead/lags.

1) Create an Auto Numbering column ("ID")
2) Create a Column to get row numbers ("Index"): MATCH(ID@row, ID:ID, 0)
3) Create a row number column for each non-FS predecessor type you plan to use:
a) FF Index = Index + "FF"
b) SS Index = Index + "SS"
4) Create a multi-select Helper column ("Dependency Helper"): SUBSTITUTE(SUBSTITUTE(Predecessors@row, "FS", ", "), ",", CHAR(10))

5) Create multi-select columns for each dependency type you plan to use:
a) FS Dependencies: JOIN(COLLECT(Index:Index, Index:Index, HAS(Dependencies@row, @cell)), CHAR(10))
b) FF Dependencies: JOIN(COLLECT([FF Index]:[FF Index], [FF Index]:[FF Index], HAS(Dependencies@row, @cell)), CHAR(10))
c) SS Dependencies: JOIN(COLLECT([SS Index]:[SS Index], [SS Index]:[SS Index], HAS(Dependencies@row, @cell)), CHAR(10))

You will now have all of your dependencies separated by type and all lags removed. You can use this to build out status logic however you'd like. Below is an example for setting a Task to "Ready" if all FS Predecessors are 100% complete and all SS Predecessors are greater than 0% complete:

=IF(COUNTM([SS Dependencies]@row, [FS Dependencies]@row) = COUNTIFS(Index:Index, FIND(@cell, [FS Dependencies]@row) > 0, [% Complete]:[% Complete], =1) + COUNTIFS(Index:Index, FIND(@cell, [SS Dependencies]@row) > 0, [% Complete]:[% Complete], >0), "Ready", "Queued")

Cheers!

Vince K.

vince@nnaviaconsulting.com
https://www.linkedin.com/in/vineshkapadia

Hi @Coby.C

I created this demo for fun. You need source data for the daily quote. Since I have only 20 quotes, I cycle through them using this formula:

=IF(INT(DAY(TODAY(1)) * 20 / 31) = [Row ID]@row, 1)

To display it on the dashboard, I used the metrics widget to make it look better. Here's the formula I used:

=JOIN(COLLECT({Quote}, {Quote of the Day}, 1))

Doesn't the quote in the image capture the spirit of our community?

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

Multiple-contact columns can be problematic to use in Smartsheet. Namely, they cannot be "put together" with a formula. There's many use cases where you need to build multiple contacts into a cell based on criteria/lookups, but there isn't a way to do that in native core Smartsheet.

There is, however, a couple of methods to do that using addons. In general I highly recommend looking at Advance packages for addons, they open a world of automation and customization beyond the core application.

Below are various mechanisms for managing multiple-contact data in Smartsheet.

Note that by "multiple-contact" I mean a Contact type column with the "Allow multiple contacts per cell" switch turned on in the column properties.

Final note: thank you to @Samuel Mueller for telling me about same-sheet Data Mesh, being great with Bridge, and other general awesomeness. Thanks to @Paul Newcome for being amazing on community and suggesting this post. And my other fellow Overachievers for making Smartsheet fun on the daily!

CORE APPLICATION

The bad news is that there's no way to "build" multiple contacts into a multi-contact column with a formula.

The only thing you can do is hand-type contacts, separated with comma, semicolon, or tab.

You cannot use Automations or Formulas to build a list of contacts.

SHEET SETUP

All the methods below leverage a single sheet with three columns.

• Column 1 is a text/number column. This can be any number of formulas that produce a series of emails, separated by comma or semicolon.
  • For example, if you need to merge multiple columns of emails into one, you'd do something like = JOIN ([Manager]:[VP], ";" ) resulting in "manager@somewhere.com; director@somewhere.com; vp@somewhere.com" in your cell
  • Alternatively, you may be pulling emails together from another sheet with a formula like =JOIN(COLLECT({Email}, {Name}, HAS(Names@row, @cell)), ";") - this formula looks at a second sheet that lists names and emails, and matches the names selected on the first sheet to the names on the second sheet to combine a list of emails. The {} items are cross-sheet references to columns on the second sheet.
  • Note that you need to combine emails, not names. Smartsheet has no way of mapping names like "Brian Richardson" or "Richardson, Brian" to an email address, and therefore will not convert a name to a contact
• Column 2 is a Contact column set to Allow multiple contacts. There's no formula in this column.
• Column 3 is an Autonumber column. If you already have one, you can use that instead of setting up a new one. Or if you have another means to setup a unique identifier for your rows, use that instead. You need a unique identifier on each row.

DATA MESH METHOD

Data Mesh to the rescue! Data Mesh is a paid addon tool for Smartsheet that is very powerful. It is definitely one of the top tools to take your Smartsheet automations to the next level.

Using Data Mesh, you can copy/sync data between two sheets, or between columns in a single sheet. Which is what we'll do here.

Key Value: Data Mesh triggers on changes, so your contacts will update almost immediately. It's also pretty easy to setup.

Key Drawback: not as flexible as Bridge, but pretty close.

1. Open Data Mesh and click New Config
2. For Source: find the sheet with your three columns, select it, and click next
3. For Target: find the same sheet, select it, and click next
4. For Mapping:
   1. Lookup Values: the Autonumber / Unique ID column
   2. Data Field 1 (Source Sheet): the Text/Number column that has your emails in it
   3. Data Field 1 (Target Sheet): the Multiple-Contact column
5. For Options: set Overwrite Existing Data to Yes. Set Execution Frequency to Update Immediately. Other settings can remain as-is.
6. Give your workflow a name and hit Next, then Add

That is all the setup you need. Now, whenever your formula creates or updates a series of email addresses, separated by semicolon/comma in the Text column, Data Mesh will fire and quickly post those emails into the Contact column as separate contacts, one for each email!

BRIDGE METHOD

You can also accomplish this using Bridge. Although somewhat complex to learn, the actual Bridge workflow for this is quite simple.

Key Value: fast and flexible - this workflow can be adjusted to do many more complex things, like looking up your contacts, manipulating the data, integrating contacts with other tools, or even running Javascript to match/map the emails to additional information.

Key Drawback: Bridge doesn't alert you if there's problems (yet), so you'll have to notice if the contacts aren't getting populated as expected.

Here's the setup:

1. You will likely want to Trigger the Bridge workflow when a list of emails is changed, or something similar. You'll need to setup a quick placeholder blank workflow first, then in Triggers under Smartsheet setup a trigger for Column Values changed or maybe Row Updated, depending on your needs. Point the trigger at your new blank workflow.
2. Add the Smartsheet: Get Row step to your workflow with the following settings:
   1. Sheet: {{runtime.sheetID}}
   2. Row: if you used the Row Update trigger, use {{runtime.event.id}}. If you used the Column Values trigger, use {{runtime.event.rowId}}
3. Add the String Tools: Split Text step to your workflow.
   1. Text: {{states.startstate.smartsheet.get_row.row.cells.name of the Text column with emails.value}}
   2. Delimeter: ; or , (whatever you used in the column to separate your email addresses)
4. Add the Smartsheet: Update Row step to your workflow
   1. Sheet: {{runtime.sheetID}}
   2. Row ID: if you used the Row Update trigger, use {{runtime.event.id}}. If you used the Column Values trigger, use {{runtime.event.rowId}}
   3. Cells: Key 1 = name of the Contact column in your sheet set to multiple contacts. Value #1 = {{states.startstate.stringtools.split_text}}

That's it! Now, whenever your comma/semicolon list of emails changes on the sheet, Bridge will read the list, split it back up, and post it into the multiple-contact column.

DATA SHUTTLE METHOD

If you don't have Data Mesh (get it!) or Bridge then you can also use Data Shuttle to accomplish the combination of emails into a multiple-contact column. It's a little more involved.

Key Drawback: Data Shuttle has to run on a schedule, with 15 minute intervals as the minimum. You cannot trigger Data Shuttle to run when your contacts change. And this relies on constant posting of attachments which fills up the attachments panel pretty quickly.

Key Value: it's relatively easy to use the UI for setup, and it alerts you if there's problems.

You will setup 2 workflows… the first will output the emails to a CSV attachment, the second will import that same attachment as contacts.

Workflow 1

1. Open Data Shuttle and click the + sign to start a new workflow
2. Select Offload workflow
3. For Source: select the sheet that you setup in Pre-Setup
4. For Target, select CSV or XLSX format and Attachment. Attachment should be pointed to the same sheet. (note - you can put it elsewhere, it's just cleaner to keep it all in the same sheet. if you put it elsewhere then just note that location, you'll need it for the second workflow). Check the "Add headers" checkbox.
5. Skip to the Mapping screen. Select the Autonumber column and the Text column with the email addresses and hit Next
6. Select Run on schedule and set to every 15 mins or whatever schedule you like
7. Click Next, give the workflow a name, then Save
8. Now Run the workflow that you just created. After a minute or so you should have an attachment on your sheet. You need to do this before doing Workflow 2 setup.

Workflow 2

1. Click the + sign in Data Shuttle to start a new workflow
2. Select Upload workflow
3. For Source: select Attachment, then browse to the sheet that Workflow 1 saved to. Select Most Recent. Data Shuttle should find the attachment from running workflow 1 in step 8 above. Select "This file has column headers".
4. For Target: select the same sheet
5. For Options: select Merge, and check only the Update checkbox
6. For Mapping: select the Autonumber column as the Unique Identifier. The mapping should already have mapped the Text column (with the emails) to itself, remove that mapping by changing to Not Mapped. Add a mapping to set the Source Text column (with the emails) to the Target Contact column.
7. On the next screen select Run on Attachment

That should do it! Now, every 15 minutes, Data Shuttle will grab all the email addresses from the Text column, dump them to a CSV attachment, then import that same attachment to post the email addresses into the contact column and create the multiple-contacts.