Trying to use JOIN, COLLECT, CONTAINS, in cross sheet formulas
I've reached a stalemate in my cross sheet formula and can't seem to get it to pull in all instances that I want it to.
I am attempting to look at two different sheets where either may contain the project number (TD Number), and have the corresponding BAR SME Engagement values returned.
=IF(HAS({WorkOrderID}, [TD Number]@row), JOIN(COLLECT({BLM BAR}, {WorkOrderID}, [TD Number]@row), ", "), IF(HAS({PL WorkOrderID}, [TD Number]@row), JOIN(COLLECT({PL BAR}, {PL WorkOrderID}, [TD Number]@row), ", ")))
I think I need to use CONTAINS, but have only been able to get HAS to work, but that only appears to work if the match is 1 to 1, as you can see in the bottom two rows. How can I make them populate and JOIN in the top row, which contains both of those project numbers?
Thank you in advance for any insight or advice!
Answers
-
You will need to use the CONTAINS function, but keep in mind that the CONTAINS function's syntax is opposite that of HAS.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!