Join/Collect Function if status of row within hierarchy is "Next Up"

Using the [Next Up] Column in row1 (the parent row) I'm trying to join the [Onboarding Effort Name] and [Due Date] text of whatever row has the "Next Up" status. The task labeled as "Next Up" will continue to change, the hope being that the "Next Up" row will auto update with the task name and due date of whichever row is labeled "Next Up". Ideally, in the screenshot below, [Next Up]1 would read "FEBS Go/No Go due by 7/29/21". Is there a formula that makes this possible?

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    Hi @Taylor Riley

    Hope you are fine, please try the following formula:

    =IFERROR(JOIN(COLLECT([Onboarding Effort Name]:[Onboarding Effort Name], Status:Status, 
    "Next Up"), CHAR(10)) + " Due By " + JOIN(COLLECT([Due Date]:[Due Date], Status:Status, 
    "Next Up")), "")

    the following screenshot shows the result:

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!