4

We have a task sheet that uses 3 checkbox columns (Sent, Received or Done, N/A).  We have a formula that counts the number of tasks and how many are completed based on the Received or Done column.  We are trying to make it so if the N/A box is checked, it automatically checks of the coinciding Received or Done box.

Please help!

Comments

if you put a formula inside the checkbox is it automated. If you click the checkbox to turn it off, then the formula will disappear and it will no longer be automated. how you solve this issue is dependent upon how the users interact with the sheet.

can you explain a little more about the sheet and potentially post a screenshot of it?

to get your toes wet and get testing something you can try this formula out.

=if([N/A]@row = 1,1,0)

I see that you have a working solution, but...

 

Here are some additional options for future reference and to account for people manually checking a Done or N/A box (if it becomes an issue).

 

You could include the criteria in the formula you are using to get the count. I am not sure exactly what you are currently using, but something along the lines of

 

=COUNTIFS([N/A]:[Received or Done], 1)

 

The above is assuming that either one or the other (not both) would be checked. This would allow for manually checking the boxes and still getting an accurate count.

 

Another option would be an additional (hidden) checkbox column with a simple

 

=IF(OR([N/A]@row = 1, [Received or Done]@row = 1), 1)

 

This will check the box in the helper column if either of them are checked, and you can pull your count from there using

 

=COUNTIFS([Helper Column]:[Helper Column], 1)