Automation on columns

MrDramaFree

I would like to set up a notification automation for when all the criteria in the column is met. For example, I have a column that includes a checkbox with the column labeled "complete". I want to receive a notification when the last checkbox in the column is checked, so that I know that all tasks have been completed on the sheet. Is this possible? Thanks!

KPH

Yes, that is possible.

You can use a COUNTIF formula to count how many rows have the complete checkbox checked. The formula would be something like:

=COUNTIF(Complete:Complete, 1)

You can use a second COUNTIF to count the number of rows in the sheet that are in use (for example COUNTIF the task column is not blank). The formula would be something like:

=COUNTIF(task:task, <>"")

Then use an IF function to compare the two and return something (in my example a 1) if the two counts match. I put this in a checkbox column so the 1 checks the box.

=IF(COUNTIF(Complete:Complete, 1) = COUNTIF(task:task, <>""), 1)

You can then use this to trigger your automation. Your automation trigger is when the cell with that formula in changes to 1 (or is checked).

You might want to create a sheet specifically for this so the trigger formula doesn't get lost or touched. You would need cross sheet references to do that.

Hope that helps.