JOIN COLLECT with OR criterion?

Samuel Mueller

Or this if you don't want the commas - and make it a multi select drop down to remove duplicates.

=IF(HAS(Positions@row, "Communications"), JOIN(COLLECT({ERT Emails}, {status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, HAS(@cell, "Communications")), char(10)), "")

+char(10)+IF(HAS(Positions@row, "ERC"), JOIN(COLLECT({ERT Emails}, {status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, HAS(@cell, "ERC")), char(10)), "")

+char(10)+IF(HAS(Positions@row, "Finance"), JOIN(COLLECT({ERT Emails}, {status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, HAS(@cell, "Finance")), char(10)), "")

+char(10)+IF(HAS(Positions@row, "HR"), JOIN(COLLECT({ERT Emails}, {status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, HAS(@cell, "HR")), char(10)), "")

+char(10)+IF(HAS(Positions@row, "Logistics"), JOIN(COLLECT({ERT Emails}, {status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, HAS(@cell, "Logistics")), char(10)), "")

+char(10)+IF(HAS(Positions@row, "Planning"), JOIN(COLLECT({ERT Emails}, {status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, HAS(@cell, "Planning")), char(10)), "")

+char(10)+IF(HAS(Positions@row, "Programs"), JOIN(COLLECT({ERT Emails}, {status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, HAS(@cell, "Programs")), char(10)), "")

+char(10)+IF(HAS(Positions@row, "REC"), JOIN(COLLECT({ERT Emails}, {status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, HAS(@cell, "REC")), char(10)), "")

+char(10)+IF(HAS(Positions@row, "Safety & Security"), JOIN(COLLECT({ERT Emails}, {status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, HAS(@cell, "Safety & Security")), char(10)), "")

Samuel Mueller

@LizTo any luck?

LizTo

@Samuel Mueller

Oh my goodness, I can't believe you took the time to sort this out for me! It is working beautifully. And making it a multiselect dropdown to remove duplicates was a great suggestion, too.

Thank you so much, that was a big help and I really appreciate it!

Samuel Mueller

Love to hear that it's working 😃