Report to show Unchecked Columns

I have a sheet that has about 12 columns with checkboxes is there a way to create a report that will show every column that has unchecked boxes by row, which is the date?

To explain further I have a report that has all the columns with names and the check boxes. I'd also like a report that only shows the columns that are not checked for each day (row).

Does that make sense?

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    The formula is going to depend on the layout of your sheet. You can either use a string of IF statements to say that if the box for [Column Name]@row is not checked, then output "Column Name", or my personal recommendation would be to use a helper row where you enter the column name into that row (for this example I will refer to row 1, but you can use whichever row you want). then you can use a JOIN/COLLECT that looks something like this:

    =JOIN(COLLECT([First Checkbox]$1:[Last Checkbox]$1, [First Checkbox]@row:[Last Checkbox]@row, @cell <> 1), CHAR(10))


    The CHAR(10) as the delimiter is a line break so each task is on its own line within the same cell for that row.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide a screenshot of the source data and indicate which rows should be pulled into the report and why?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Andrea Westrich
    Andrea Westrich ✭✭✭✭✭✭

    Hi @Paul Newcome Attached is a screenshot of daily tasks that are supposed to be checked off. I'd like to run a report each day that shows only those tasks that were not checked for the last 7 days.

    What I'm trying to do here, and having a hard time setting it up, is to send people daily checklists to complete and to run daily reports so managers can see how they and their direct reports are doing.

    Can I provide any additional information?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    So basically you want to run a report that shows rows that have a date in the past seven days and where the Daily Points is less than 11?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Andrea Westrich
    Andrea Westrich ✭✭✭✭✭✭

    @Paul Newcome, no...that would be simple. I want to run a report that only shows the columns that are not checked in the rows where the date is in the last 7 days. If I could just get it to show the columns where there is not a checkbox, I'd be happy.

    Thanks

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    There currently is not a way to conditionally show certain columns in a report. Either you would have to show all columns or you could insert a new column into the source sheet that will list out each of the column names in a text string and have that shown in the report.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Andrea Westrich
    Andrea Westrich ✭✭✭✭✭✭

    @Paul Newcome what would that formula look like? How well would that display in a report for a dashboard?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    The formula is going to depend on the layout of your sheet. You can either use a string of IF statements to say that if the box for [Column Name]@row is not checked, then output "Column Name", or my personal recommendation would be to use a helper row where you enter the column name into that row (for this example I will refer to row 1, but you can use whichever row you want). then you can use a JOIN/COLLECT that looks something like this:

    =JOIN(COLLECT([First Checkbox]$1:[Last Checkbox]$1, [First Checkbox]@row:[Last Checkbox]@row, @cell <> 1), CHAR(10))


    The CHAR(10) as the delimiter is a line break so each task is on its own line within the same cell for that row.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Andrea Westrich
    Andrea Westrich ✭✭✭✭✭✭

    @Paul Newcome the helper row and formula worked perfectly! However, when I created a report, it's not very legible on the dashboard. Any ideas on how to make this a list (meaning returns to the list goes down)? Or I'm open to other ideas.

    Thanks

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I'm not sure I understand exactly what you mean with your last question. Did you use the Wrap Text function on the column in the sheet?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Andrea Westrich
    Andrea Westrich ✭✭✭✭✭✭

    Not super pretty, but that made it work. Thanks for your help, especially with the formula and for explaining it so well. That made it easy to plug into my sheet!!

    I appreciate the help!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!