Formula to pull data from a multi-value column

I need help figuring out how I can use formulas to populate a column that is referencing a multi-value column. I am hoping someone can assist. I have the following two sheets--"Resource List" and "Crew/Resource Assignment."
Resource List: The Team Members on the other sheet are in a multiple contact list. On this sheet, I want to auto-populate the Crew # column from the other sheet. I am showing the two formulas I have tried to accomplish this. Thoughts?
Crew/Resource Assignment: Here I want to pull the skills listed on the "Resource List" sheet for each team member. For example, the first two Names on the Resource List are assigned to Crew #1. So the skills here should show A, B, C, D (and a second C, if necessary). I am hoping that this can be done similarly to the previous solution.
I appreciate any help that anyone can lend here. It is baffling me!
Steve
Steve Reed, Smartsheet Practice Lead/Architect
Adapture, Smartsheet Platinum Partner
sreed@adapture.com | 843.422.1484
North America Partner Win of the Year: 2023 and 2024
Answers
-
Try using a JOIN/COLLECT combo to join all of the cells together based on the matches (HAS function should work for that). If you put the formula into a multi-select dropdown, it will filter out any duplicates and you should get "A, B, C, D" instead of "A, B, C, C, D"
Help Article Resources
Categories
Check out the Formula Handbook template!