Please I need help with a formula and have tried to outline what Im trying to do below


  • isabonita
    isabonita ✭✭✭

    Hi @Hobo

    You can do this with a JOIN COLLECT...

    =JOIN(COLLECT([B - Official 1]@row:[D - Official 3]@row), ",")

    Let me know if you end up with extra commas.. and I can help get rid of those, too.

    If you want each person on a new line - rather than comma-separated - you just use the CHAR(10) for the separator (no quotes around it) rather than the comma.

  • Hobo
    Hobo ✭✭

    No this didn't work at all in sheet 2 the list of officials is already pre defined and what i'm trying to do is add what venue they have been allocated to in the previous sheet 1. Some officials in the predefined sheet 2 list may not be allocated at all

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I would insert a multi-select dropdown into sheet 1 and then use this formula:

    =JOIN(COLLECT([B - Official 1]@row:[D - Official 3]@row, [B - Official 1]@row:[D - Official 3]@row, @cell <> ""), CHAR(10))

    Then in sheet 2 you can use something like this:

    =IFERROR(INDEX(COLLECT({Sheet 1 Venue Column}, {Sheet 1 Helper Column}, HAS(@cell, [B - Official]@row)), 1), "")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!