Is it possible to match multiple values with in a single cell?

John Kaler

I'm sorry if this is long winded.

I am creating a workflow process where a file will need to be emailed to multiple users at different locations. There is a drop down that lists the locations, in this example it's 3 separate locations, Portland, Minnesota, and Houston. I'd like to keep this drop down to allow multiple values per cell. Each location has a distribution list of email addresses on another table. Is it possible to use Index / Match or like formula to pull the email address from the reference table and match the values to the locations within a single cell? Location contact will need to provide all the email addresses for all 3 locations which can be multiple values per location. Is this possible?

Paul Newcome

It is not currently possible to use a formula to populate multiple usable contacts within a single cell.

Paul Newcome

It is not currently possible to use a formula to populate multiple usable contacts within a single cell.

John Kaler

Thank you

Cleversheet

@John Kaler , you might be able to use a JOIN(COLLECT()) formula to achieve your intent, though perhaps you’d need to split the solution into multiple columns and then combine them once you have your target results separately.

In my case, as you’ll see in the screenshots below, a person with multiple semesters associated with their email was transmuted onto a list for each such semester. For instance, note that Carlson shows up next to AY23F but Silberstein doesn’t, and vice versa for AY23S. (Disregard that for other purposes Silberstein is grayed out on one image.)

Not positive your specifics are solvable in a similar way, but I wouldn’t give up hope entirely.

The formula I used to achieve the upper image from the data in the lower image will be found at “GATHER MULTI-SELECT ENTRIES AND SHOW THEM IN ANOTHER PLACE ON SAME SHEET” in my Smartsheet Arsenal found at: https://docs.google.com/document/d/10eviPkYegA7bYBkWaXSNRM6hCOpahyZaVjSv9WggXJA/edit

Paul Newcome

@Cleversheet Does this work to output multiple USABLE contacts so that they can be used for automations?

In theory you could use the JOIN/COLLECT to bring the list of email addresses together, but then you would need to parse them out into their own columns to make it so they can be used in automations (and then your automation would of course need to be set up to send to each of these contact type columns).

Cleversheet

I fear you're right about that, Paul. I can successfully display in Sheet A the list derived using that formula in Sheet B, and everything looks as it should. However, the automation won't [yet] process it. I'll follow up with Smartsheet Support to see if there's something I can do to finesse it. For instance, I realized that I could insert a ", " (including space character) rather than a CHAR(10) to have it show the list of emails as Sheet A's Contacts column should want it, but it still doesn't seem to swallow them right.

Paul Newcome

@Cleversheet This is actually expected behavior. You would need to parse them out into their own individual columns. I actually have a thread regarding this tucked away in here somewhere. I am trying to find a link now and will post it as soon as I can find it.

John Kaler

Thank you both @Cleversheet @Paul Newcome. I think I will go another route with this one. I appreciate you both looking into it.

Cleversheet

@Paul Newcome,

Although it may be "expected behavior" I'd regard it as sub-optimal behavior. My use case, and I think John Kaler's, are instances where from a process standpoint it just makes sense to populate a Contact List column with formula results (from any source including cross-sheet) and to have those entries actionable in workflows. I'll submit an enhancement request accordingly.

Thanks for your input on this, Paul. And @John Kaler I wish you well in solving your issue.

Cleversheet

By the way, it's multiple contacts that breaks the system. I'm able to suck in a single contact and the workflow works fine.

Paul Newcome

@Cleversheet I agree that being able to do it for multiple contacts would be ideal. And yes. You should be able to populate singe contacts (currently).

I was just letting you know that the automation isn't going to send and support won't be of any help to hopefully save you the time I spent banging my head against a wall on this one (testing and working with support).

The is ONE way to use a formula or automation but it does not scale very well at all and provides very little flexibility. Basically your reference sheet would need to be set up with every possible combination in their own row. Then you can use an INDEX function to pull in the single cell that contains the combination you need.

Or you could set up individual automations for every combination.

Not very ideal, efficient, scalable, flexible, feasible, etc., but still technically a possibility.