Automation for "all boxes checked but this one"

michael_ord

Hi Group!

We use a very simple set of workflows (essentially tasklists) where people from various departments receive an Automation Workflow alert when someone clicks a cell in a Done column. So it's something like "The trigger is When Rows are changed, and Done is checked and Row ID is X". 

Now I need a method to trigger an alert when all but the final row's Done columns are checked.  

So I have 5 rows, and there's a Done column. And when all cells in that column are checked except the last one, we fire off an alert to someone. (see VERY basic image). Best way to write conditions for this? 

Thanks.

Andrée Starå

Hi Michael,

Are they always completed in the same order, or do you want it to trigger when there's all but one completed?

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

michael_ord

Hi Andree- These tasks are completed in random order. My intention is to signal to someone that all the tasks have been completed and they need to inspect the content, then move the sheet to a new workspace. So (as in my example), 4 actions are completed and marked "Done" - which creates the trigger for the responsible party of the 5th task to inspect, mark "Done" and move the sheet. 

Paul Newcome

You could use a combination of COUNTIFS in a separate checkbox column (or if you have a free cell somewhere on the sheet use formatting to hide the data and produce a specific text).

.

=COUNTIFS([Task Name]:[Task Name], ISTEXT(@cell)) - 1

This will give you the total number of tasks (and ignore the empty rows) then subtract 1 from it which will give you how many boxes need checked.

.

=COUNTIFS(Done:Done, 1)

This will give you the number of Done boxes that are checked.

.

Comparing the two numbers together in an IF statement will allow you to populate a filed with specific text or a checkbox or flag or whatever you chose when those numbers match meaning there is only one task left to complete.

=IF(COUNTIFS([Task Name]:[Task Name], ISTEXT(@cell)) - 1 = COUNTIFS(Done:Done, 1), "Text or flag trigger here")

Andrée Starå

Happy to help!

I saw that Paul answered already!

Let me know if I can help with anything else!

Best,

Andrée