Using JOIN/COLLECT and an OR?

axwestlake

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!

Michelle Choate 2

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 2

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"), ", ")

axwestlake

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. 🙂