Multi Select Look up

Scott.Niedzwiecki

I'm trying to see if anyone has any ideas of a formula that can been written for the following example.

I'm trying to get the contact(mail) in sheet 2 to populate sheet 1(Approval Seq 1 Contact) column. 

 There is 2 criteria (Division and Seq.1) on sheet 1 need to match (Division, Sequence #) on sheet 2 to return the contact (Contact (Email) to Sheet 1 (Approval Seq 1 Contact). 

Sequence # is a Multi Select (dropdown list)

 

Thanks for the help

Eric M Oliveira

Hello,

 

Happy to help create a formula that looks up a contact value from a source when the Division Column and Sequence Column match on both sheets. To achieve this you can utilize a cross-sheet JOIN(COLLECT()) formula similar to the below. 

 

=JOIN(COLLECT({Source sheet Contact Column}, {Source sheet Division Column}, Division@row, {Source sheet Sequence Column}, Sequence@row), " , ")

 

The above formula would be placed in the contact column you which the contact to populate in. {Source sheet Contact Column}, {Source sheet Division Column}, and {Source sheet Sequence Column} are cross-sheet references that you will want to link to their respective columns on the source sheet, to achieve this highlight them once in the contact cell and select 'Reference Another Sheet'. Division@row and Sequence@row, will ensure that the criteria the formula is looking for on the source sheet are the value that appears in the columns on your recipient sheet. The topics we discussed are further outlined by the Help Center articles below. 

 

JOIN(COLLECT()): https://help.smartsheet.com/function/collect

 

@row: https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

 

Cross-Sheet Formulas: https://help.smartsheet.com/learning-track/smartsheet-advanced/cross-sheet-formulas

 

Have a wonderful day. Thank you for contacting Smartsheet Support.

Cheers,

Eric

Smartsheet Technical Support

Scott.Niedzwiecki

Eric,

Thanks for the helpful tips but for some reason it returns the contact as a text string and not the actual contact in the column.  Do you have a trick or work around to return the actual contact?

Thanks 

 

Scott

Kelly Drake

I'm having this exact problem!

Join/Collect works to bring the data but its one solid text string instead of the separate options that are part of my list of options.

Andrée Starå

Hi Kelly,

@Kelly Drake

Try something like this.

In this example, the values are stored in row 1, and when the statement is true, we collect the values together, and then we use CHAR10 to break them up, so they become their options in a multi-select list.

=JOIN(COLLECT(ColumnOne$1:ColumnThree$1; ColumnOne@row:ColumnThree@row; @cell <> ""); CHAR(10))

The same version but with the below changes for your and others convenience.

=JOIN(COLLECT(ColumnOne$1:ColumnThree$1, ColumnOne@row:ColumnThree@row, @cell <> ""), CHAR(10))

Depending on your country, you'll need to exchange the comma to a period and the semi-colon to a comma."

Would that work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer! 

McKenna Karasek

Hi!

I know this is an older forum but I have been trying to find a solution for pushing many single check columns to the selections in a multi-select dropdown and have been having issues. When using the join(collect...) formula from above (and other similar forum discussions) I only get a true/false return...for each column so it looks like "false true false false false true true false false...etc" but I need the column names to populate as values instead of the true/false. I'm unsure how to include this formula and any assistance would be greatly appreciated!

Genevieve P.

Hi @McKenna Karasek

Is this in the same sheet? I would personally write a long nested IF statement to check if the box is checked or not and then if it is, return the Column Name:

=IF([Column 1]@row = 1, "Column 1" + CHAR(10)) + IF([Column 2]@row = 1, "Column 2" + CHAR(10)) + IF([Column 3]@row = 1, "Column 3" + CHAR(10))

And so on.

Cheers,

Genevieve

Scott.Niedzwiecki

Genevieve

It was close... I believe I got it to work with using the join and collect function.

Thanks for the help.

McKenna Karasek

Yes I was able to get it to work with a JOIN(COLLECT) after setting an absolute row reference that was a copy of the col headers (there were 37 col so a nested statement was an unwieldy option). Thank you for your input!