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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!