I'm looking into setting up a small process that would allow my team to essentially selectmultiplechoices from a drop down in-order tocombinethe various results that those selections represent in a separate sheet. The key is that we want tocombinethose options in asingle 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 butONLYif we have aSINGLEselection
To better visualize what we are trying to do please refer to the attached examples.
NOTE:In this exampleProjectASheet is used only as a sheet that stores references.
USE CASE:
InProjectBSheet, 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 "cellthat 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 butONLYif we have aSINGLEselection.
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 statementper selectionso that it checks through all of your multi-select options.
Genevieve P. Employee Admin
