Trying something similar to collect, join, and populate full state names (eg, Alaska, Arizona) in a target sheet from a reference sheet based on multi-selected state abbreviations (AK, AZ) in the target sheet:
Currently Smartsheet formulas cannot look into a multi-select cell and parse out the individual values to then search for each separate value in a reference column. The formula will look for a matching cell withallvalues matched in the reference sheet, to then bring back a corresponding cell.
You could set up your reference sheet to have every single possible combination in a multi-select column and then the equivalent text values in a second column, however if you have a large number of states to populate then this could be difficult.
An alternative would be to use multiple IF statements to search for each possibility and return the individual state name written out:
@Genevieve P.I saw this reply from several months ago, do you know if there is progress for the formulas to parse out individual values from a multi-select cell? Specifically looking for this to work.
There isn't a way to parse out multiple values in one cell into multiple rows, or into individual matching values, no. However for your formula, it sounds like you need the opposite.
If {strengths} is multi-select but strength@row is a single value, you can use the HAS function to see if the cells in the {strengths} columnhavethat one selection among others:
我有一个类似的问题加入/胶原ect function to work using a multi-select cell. I tried using the HAS() function you described, but it doesn't seem to be working. I'm actually using two sheets in my application. I am comparing'Sheet 1' and 'Sheet 2'to see if two criteria are met (Project Name and Revision). If these two criteria are met, my goal is to collect every row in the 'enter design comment(s)' column in 'Sheet 1' and join them into one cell in the appropriate 'design comment(s)' column in 'Sheet 2'. I've included the formula I tried using (without success below). I tested the overall functionality of the formula by trying entries that aren't included in the drop-down multi-select option and it worked. I just can't seem to get it to work for the multi-select portion. Hope my description below makes sense.
=JOIN(COLLECT({Design Comments}, {Project Name}, HAS(@cell, [Project Name Description]@row), {Revision}, =[Revision Log]@row), " | ") -This formula is in the 'Design Comment(s)' cell of 'Sheet 2'
If you were meaning that you want toenter inmulti-select values, then instead of using " | " as a separator in your JOIN function you can use CHAR(10) like so:
The {Project Name} column from Sheet 1 is indeed the multi-select. I was able to get it to work! The project name description from sheet 2 did not exactly match the options from the multi-select field because I inadvertently added a space somewhere along the way. Thanks for the help. It works perfectly now!
Genevieve P. Employee Admin
Conv_Eng ✭
