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

  • Michelle Choate 2
    Michelle Choate 2 ✭✭✭✭✭✭
    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

  • Michelle Choate 2
    Michelle Choate 2 ✭✭✭✭✭✭
    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

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!