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! Book time with me here: https://calendly.com/michelle-choate
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! Book time with me here: https://calendly.com/michelle-choate
-
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
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!