Do I need INDEX?

This discussion was created from comments split from: Index Collect Formula.


  • Lauren P

    Hello all, I'm not sure if I need an INDEX in my formula. Here is what I'm trying to do in a single cell. I want to list in a single cell the content of TITLE for all the STATUS = Active AND RAIDL_TYPE = Decision, Issue, or Risk. I want to order by Decision, Risk, Issue if possible. I want a carriage return as delimiter.

    I'm partly there - this formula does work if I am only looking for "Decision".

    =JOIN(COLLECT(Title13:Title50, Status13:Status50, "Active", [RAIDL_Type]13:[RAIDL_Type]50, "Decision"), CHAR(10))

    What is throwing me off is the Order By and showing the multiple conditions for Decision, Issues, orRisk. I keep getting errors when I try to add more to this formula. Any suggestions?

  • bisaacs
    bisaacs ✭✭✭✭✭

    Hey @Lauren P,

    So I think you can use JOIN/COLLECT like you're doing, you'll just add an OR statement within the criteria portion of your formula:

    =JOIN(COLLECT(Title13:Title50, Status13:Status50, "Active", [RAIDL_Type]13:[RAIDL_Type]50, OR(@cell = "Decision", @cell = "Issue", @cell = "Risk)), CHAR(10))

    That way it should be looking for an Active Item that either has a Status of Decision, Issue, or Risk (which is I believe what you're trying to do.

    Hope this helps!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Lauren P You can use an OR function in the JOIN/COLLECT to include multiple statuses, but the output will not be grouped by status. To group by status, you would need to write out separate JOIN/COLLECT statements for each status and then "add" them together with a line break in the order that you want them in.

    =JOIN(COLLECT(first status), CHAR(10)) + CHAR(10) + JOIN(COLLECT(second status), CHAR(10)) + CHAR(10) + JOIN(COLLECT(third status), CHAR(10))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!