Report to show Unchecked Columns

Andrea Westrich

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?

Paul Newcome

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.

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?

Andrea Westrich

Screenshot 2021-05-17 065755.jpg

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

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?

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

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.

Andrea Westrich

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

Paul Newcome

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.

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

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?

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

Happy to help. 👍️