How to list all rows that have at least one checkbox?

Options

I am in a conundrum. I am trying to pull the names from the Name column, for each row that has at least one checkbox. The list of names corresponds to people who have used this business unit and the check boxes denote if they have used it in any quarter of 2023.

I can't seem to get the INDEX(COLLECT()) to work. Here is what I think should work for just looking at the 2023 Qtr1 column checkboxes, and ignoring the others, but will not:

=INDEX(COLLECT({CAMP 2023-locked Range 2}, {CAMP 2023-locked Range 1}, 1), @row)

"CAMP 2023-locked" is the sheet name, Range 2 is the Name column, and range 1 is the 2023 Qtr1 column. I know this doesn't analyze all columns of check boxes, I can add the OR() function easily enough if I can get this to work.

I can count the number using the COUNT() function, but this is not a solution, because I would like to do this with several sheets (each from their own business unit), and then see a list of the unique names across all business units (essentially get rid of duplicates from the separate lists) that have at least one checkbox.

Using JOIN(COLLECT()), I can get the list, but it only prints in one cell, and I can't find a way to parse it by the delimiter I gave it.

What am I missing?

Thanks for any and all help!

Best Answer

  • Zachary Ziegler
    Zachary Ziegler ✭✭✭✭
    Answer ✓
    Options

    So I was able to get a conditional test case working with one column in consideration using the following formula, which could definitely be edited to fit your use case. I was able to use the formula =IF(CheckboxColumn@row = 1, "checked", "unchecked") to show checked for a checked checkbox and unchecked for one that is not checked. You could probably do something similar except instead of checking against single variable, you could sum the value of each column and display usage by number of quarters, or if the output you are looking for is a list of names you can have it display Name@row and then just copy the column worth of outputs to get a comprehensive list of people who have used it in the last year just by setting that as the column formula. I might need a bit more context if this doesn't quite answer what you are looking for but let me know and I'll try to take a deeper dive.

Answers

  • Zachary Ziegler
    Zachary Ziegler ✭✭✭✭
    Answer ✓
    Options

    So I was able to get a conditional test case working with one column in consideration using the following formula, which could definitely be edited to fit your use case. I was able to use the formula =IF(CheckboxColumn@row = 1, "checked", "unchecked") to show checked for a checked checkbox and unchecked for one that is not checked. You could probably do something similar except instead of checking against single variable, you could sum the value of each column and display usage by number of quarters, or if the output you are looking for is a list of names you can have it display Name@row and then just copy the column worth of outputs to get a comprehensive list of people who have used it in the last year just by setting that as the column formula. I might need a bit more context if this doesn't quite answer what you are looking for but let me know and I'll try to take a deeper dive.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi,

    I hope you're well and safe!

    I recommend adding a so-called helper column that you can reference with the cross-sheet formula. (can be hidden)

    The formula in the helper column would look something like this.

    =COUNTIFS([2023 Qtr1]@row:[2023 Qtr4]@row, 1)

    Would that work/help?

    I hope that helps!

    Be safe, and have a fantastic day!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • amhenkel
    Options

    Hi Zachary, thanks for the insight! I was able to achieve this while holding onto the unique names through some helper columns. I wanted the names to stay intact so that I could use the DISTINCT() function later to remove duplicates. Thanks for taking the time to help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!