Output list of checked boxes

Options

Hello,

I have a sheet with multiple columns, where each row has a number of potential tasks marked in check boxes in columns:


What I'd like to do is summarize in a separate page the boxes that were checked (ie. Number 74 =CD Pick Up, 78 Tall=AB Pick Up, CD Pick Up and EF Pick Up). I am assuming that I need to use JOIN COLLECT, but I am having trouble ensuring I pull back data for specific Number column items. What formula should I use?

Thank you all in advance for any assistance!


Best,

Christina

Answers

  • John_Foster
    John_Foster ✭✭✭✭✭✭
    Options

    Hi @Christina Chandler,

    I am unsure on the JOIN COLLECT formula especially because the values of the cells are either 1 if checked or 0 if unchecked. The formula below may be of use. It is long, but includes every combination.

    =IF(AND([AB Pick UP]@row = 1, [CD Pick Up]@row = 1, [EF Pickup]@row = 1), "AB Pick Up, CD Pick Up and EF Pick Up", IF(AND([AB Pick UP]@row = 0, [CD Pick Up]@row = 1, [EF Pickup]@row = 1), "CD Pick Up and EF Pick Up", IF(AND([AB Pick UP]@row = 0, [CD Pick Up]@row = 0, [EF Pickup]@row = 1), "EF Pick Up", IF(AND([AB Pick UP]@row = 1, [CD Pick Up]@row = 0, [EF Pickup]@row = 1), "AB Pick Up and EF Pick Up", IF(AND([AB Pick UP]@row = 1, [CD Pick Up]@row = 0, [EF Pickup]@row = 0), "AB Pick Up", IF(AND([AB Pick UP]@row = 1, [CD Pick Up]@row = 1, [EF Pickup]@row = 0), "AB Pick Up and CD Pick Up", IF(AND([AB Pick UP]@row = 0, [CD Pick Up]@row = 1, [EF Pickup]@row = 0), "CD Pick Up", "")))))))

    If anyone else has a better solution, I would love to see it.

    Hope this helps!

    John

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!