Need help with whether to use Datamesh or Formula

Jodi Burdette

I have a sheet that we would like to pull info from another sheet and I'm having difficulty figuring out the best way to do it. My target sheet has a bulletin that refers to several TOPP numbers. I would like to pull email addresses from source sheet for those TOPP numbers listed in target sheet. I can figure out how to do one, but not for multiple without adding separate columns for each TOPP number. The TOPP numbers in target sheet will be manually entered when a new bulletin is started, and it could be 1 TOPP number or it could be 3 or more.

From what I have read, I can't have multiple numbers in one cell for VLookup or Datamesh to work. Is that correct?

Paul Newcome

Are you able to provide some screenshots for context?

Jodi Burdette

I'm able to pull the email info using Datamesh as long as I only have one TOPP/SOP number in a cell. Just not sure I'm utilizing the best option to achieve the results needed.

Paul Newcome

You can use a combination of a formula and DataMesh to pull in multiple contacts:

https://community.smartsheet.com/discussion/123433/combine-emails-into-a-multiple-contact-column-multi-contact-column

Brian_Richardson

@Paul Newcome we are getting a lot of mileage out of that article! :-)

Clearly it's a pressing need for SS to address in enhancements.

Jodi Burdette

Thanks for the info article. Hoping it will help. Just have to sit and figure it all out. I was hoping to pull email contacts from another sheet based off a number entered triggering it in the new sheet. Then was hoping to set up a workflow on the email column. Was trying to save user from having to enter the emails since I have them in another sheet. Hope that makes sense.

Paul Newcome

@Jodi Burdette That does make sense. There are a number of formulas that can be used to pull emails over from a reference sheet. In your case it is most likely going to end up being a JOIN/COLLECT combo of which there are a number of posts here in the Community already (and we can help tweak if you post something that you've gotten started with).

Once you have the formula established, the above linked post will get you the rest of the way. There is a walkthrough of DataMesh there.

@Brian_Richardson For sure. It has long been asked for. Hopefully this will bring more visibility to the issue. Although… We could be just helping them sell more add-ons to people who have this need. Haha.

Jodi Burdette

Thank you!! If I have any issues with the Join/Collect formula, would you be able to help? I will definitely review the articles first. Thank you for the quick response!

Paul Newcome

Most certainly. Based on your screenshot though, we may need to create a helper column (multi-select with a SUBSTITUTE function to replace the text delimiter in the number column with a line break with is CHAR(10)), but the most important thing is consistency in data collection. I see one row that has multiple numbers uses a comma delimiter, but another row is using a semi-colon. Both can be accounted for, but the more variations you have, the more complex the solution becomes.

Jodi Burdette

Yea that was just me trying different things to get it to work. LOL! Let me get it cleaned up in the next day or two and maybe I could even upload my sheet for you to view to see if I'm on the right track. ??