formula for contact list to show multiple values from source sheet
Hi, im hoping someone can help me.
I made a source sheet to List the Team Leaders (contact list) in one column and the teams in another column
i then did index match formula in my main sheet to pull up the Leader (contact list - multi select) depending on what teams they choose.
When they choose multiple, it would just say #no match
ive tried to look at different collect join formulas but im either not writing my formulas right or they wont work in my scenario.......
this is the original formula =INDEX({Ministries and Ministry Team Leaders Range 2}, MATCH(Ministry@row, {Ministries and Ministry Team Leaders Range 3}, 0))
does anyone know if there is a way for it to list all the team leaders for all the teams they chose?
Kind Regards
Christina Paton
Answers
-
Hi @ChristinaP,
You can use INDEX & COLLECT for this:
=INDEX(COLLECT({Ministries and Ministry Team Leaders Contacts}, {Ministry}, [Ministry]@row), 1)
Hope this helps, but if you've any problems/questions then just post! 🙂
-
Hi @Nick Korna
Thank you for your reply :)
I have tested it however seem to have the same issue where it works for one but when there is multiple ministries it says #Invalid Value instead of showing the multiple contacts.... because my sheet uses multi select dropdown list i made sure the source sheet was multi select dropdown list also but it didnt make a difference either....
-
Typically you would need the JOIN function instead of the INDEX function. INDEX only pulls one entry. JOIN joins together multiple entries.
The challenge though is this... We cannot currently use a formula to join together multiple usable contacts into a single cell even when the column is formatted to allow for multiple contacts. It will show all of the contacts listed out, but it will be stored as a text string and can't be used as contacts in things like automations.
You will have to leverage the API or the premium add-on Bridge to get multiple variable usable contacts into a single cell.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Oh such a bummer. We are on the old team plan (and small NFP that can’t afford to upgrade). So many frustrations over these last 7 years with most tasks unable to do something.
Could you please show me the join function formula that would at least show as text (as I had unsuccessfully tried that in combinations also)…
Thank you so much for your time
Christina
-
No matter the level of your plan, no one is able to use a formula to output multiple usable contacts like this.
The JOIN/COLLECT would be similar to your existing INDEX/COLLECT except (of course) you would use the JOIN function in place of the INDEX, and you would put your delimiter in place of the 1 at the end. My suggestion would be CHAR(10) for the delimiter. That is a line break and keeps things looking a little cleaner when you use the wrap text function on the column.
The biggest difference is using a HAS function in place of Ministry@row. It would look more like this:
HAS(@cell, Ministry@row)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Paul,
I tried this:
=JOIN(COLLECT({Ministries and Ministry Team Leaders Range 2}, {Ministries and Ministry Team Leaders Range 4}, HAS(@cell, Ministry@row), CHAR(10)))
but it said incorrect argument set
I dont know how to use the HAS(@cell corrrectly and tried a few different things but when i removed it/changed to this:
=JOIN(COLLECT({Ministries and Ministry Team Leaders Range 2}, {Ministries and Ministry Team Leaders Range 4}, Ministry@row), CHAR(10))
it is blank when there is multiple :(
is it to do with the multiselect lists?
I changed the criteria to just be witten names and no contact cells but still wont work so thought maybe its the multiselect lists - cant change this as people need to select them in their form....
guessing i will just have to leave this one....
-
You have a misplaced closing parenthesis. You closed the HAS function but didn't close the COLLECT function before entering your delimiter.
Take one closing parenthesis from the very end and move it to immediately before that very last comma.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
ah thanks for clarifying - but yeah still doesnt work - just shows blank for multiple - guessing its to do with the multi-select dropdown list
-
Oh my golly i got it to work by moving the has function around
=JOIN(COLLECT({Ministries and Ministry Team Leaders Range 2}, {Ministries and Ministry Team Leaders Range 1}, HAS(Ministry@row, @cell)), "; ")
the char 10 didnt do a new line so i just did ; to seperate
-
CHAR(10) now works :) my cells werent set to wrap so it just left them on the same line hahaha
=JOIN(COLLECT({Ministries and Ministry Team Leaders Range 2}, {Ministries and Ministry Team Leaders Range 1}, HAS(Ministry@row, @cell)), CHAR(10))
thanks for your help with everything
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 283 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!