Using JOIN/COLLECT and an OR?
I'm trying to write a formula using the JOIN and COLLECT options. However, I've hit a sticky point when I want to include an OR criterion on the COLLECT portion.
My current formula (which works perfectly) is:
=JOIN(COLLECT({Assets Range 1}, {Assets Range 2}, [Tool]@row, {Assets Range 3}, "Live"), ", ")
However, I want to limit the Assets Range 3 to include "Live" or "Scoped". I can't figure out how to write this to include the OR option.
Any ideas? Thanks in advance!
Best Answer
-
First, it is always best practice to name your ranges. That way when you are trouble shooting later, you know what you are looking at. Or if you delete the cells with references, and you are managing references for the sheet, you know what they are. So for instance your "Assets Range 2" would be something like "Assets - Tools" something like that.
Second, when using an OR as your criteria, use @cell. So in your formula:
=JOIN(COLLECT({Assets Range 1}, {Assets Range 2}, [Tool]@row, {Assets Range 3}, OR(@cell="Live", @cell="Scoped"), ", ")
Michelle Choate
michelle.choate@outlook.com
Always happy to walk through any project you need help with!
Answers
-
First, it is always best practice to name your ranges. That way when you are trouble shooting later, you know what you are looking at. Or if you delete the cells with references, and you are managing references for the sheet, you know what they are. So for instance your "Assets Range 2" would be something like "Assets - Tools" something like that.
Second, when using an OR as your criteria, use @cell. So in your formula:
=JOIN(COLLECT({Assets Range 1}, {Assets Range 2}, [Tool]@row, {Assets Range 3}, OR(@cell="Live", @cell="Scoped"), ", ")
Michelle Choate
michelle.choate@outlook.com
Always happy to walk through any project you need help with!
-
Thank you Michelle! That's exactly what I needed. And yeah, I know I should name my ranges. I just haven't done that yet. 🙂
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!