Data Mesh. How to pull multiple data points based on multiple selections into one cell

Hi Everyone,

I'm looking into setting up a small process that would allow my team to essentially select multiple choices from a drop down in-order to combine the various results that those selections represent in a separate sheet. The key is that we want to combine those options in a single cell.

Does anyone know if this is possible with data mesh and how to do it? My team was able to make this process work but ONLY if we have a SINGLE selection

To better visualize what we are trying to do please refer to the attached examples.

NOTE: In this example ProjectA Sheet is used only as a sheet that stores references.

USE CASE:

In ProjectB Sheet, the user selects what numbers they want from Test Column (1,2,3) and in return they can quickly pull the needed data into the "Data from ProjectA "cell that each number represents for their choices.

Can this be done? or is this a limitation in smartsheets? as mentioned before we were able to make this process work but this process work but ONLY if we have a SINGLE selection.

Looking forward to hearing your recommendations

Find more posts tagged with

Finance

Support

Solution Center

DataMesh

Accepted answers

All comments

Genevieve P.

Hi @TestUser123

DataMesh and formulas won't be able to parse out the multiple selections and search for an individual match per-selection across sheets. As you've found, it will look for a match for the entire cell, which is why it works when you only have one selection.

1 - One option would be to have your reference sheet (Project A) list all possible combinations and outputs as part of your reference table, with the Test Column set as multi-select so you have exact matches.

2 - An alternative would be to build a formula that searched for each individual match and then brings the data in across sheets.

Using your examples, this is how I'd write one IF statement to check for the "1" selection:

=IF(HAS([Test Column]@row, 1), INDEX({ProjectA Column2}, MATCH(1, {ProjectA Test Column}, 0)))

Then any time "1" is selected, it will bring back 1's details using INDEX(MATCH. You would need to build out an individual IF statement per selection so that it checks through all of your multi-select options.

=IF(HAS([Test Column]@row, 1), INDEX({ProjectA Column2}, MATCH(1, {ProjectA Test Column}, 0)) + CHAR(10)) + IF(HAS([Test Column]@row, 2), INDEX({ProjectA Column2}, MATCH(2, {ProjectA Test Column}, 0)) + CHAR(10)) + IF(HAS([Test Column]@row, 3), INDEX({ProjectA Column2}, MATCH(3, {ProjectA Test Column}, 0)) + CHAR(10))

Screen Shot 2022-08-26 at 12.45.45 PM.png

Let me know if that makes sense and will work for you!

Cheers,

Genevieve

Archie123456

I have a similar issue. I am trying to add multiple serial numbers from one sheet to another sheet moving from 5 serial numbers in 5 cells to 5 serial numbers in 1 cell. All these serial numbers are connected through a specific site location. Can you help with the code for the cross sheet reference. I will add a screenshot below. The two columns used are called 'Site Locations' & 'Serial Numbers'.

Genevieve P.

Hi @Archie123456

Try using a JOIN(COLLECT combination, like so:

=JOIN(COLLECT({Column to bring back}, {Column with matching criteria}, [Matching Criteria]@row), CHAR(10))

Cheers,

Genevieve

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.