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

Options

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

Answers

  • Genevieve P.
    Options

    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))



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

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Archie123456
    Options

    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.
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now