I have three columns:

  1. Task (text)
  2. Assigned (checkbox 1)
  3. Done (checkbox 2)

I want to create a ticket task sheet. If task is in progress, check checkbox 1 and task cell will turn yellow. I'm able to make this work.

But what I can't get it to work is: If task is completed, check checkbox 2, and will automatically unchecked checkbox 1, also, will change task color to green instead of yellow. I tried to use IF, SUBSTITUTE, or REPLACE but don't know how to get it to work.

Share and Edit link: https://docs.google.com/spreadsheets/d/1IT-GoMUwbNgVcr1kJMtR-1GcWVQREjiRav7kweoeZcA/edit?usp=sharing

Best regards


2 Answers 2


switch can do the same job.


Tests an expression against a list of cases and returns the corresponding value of the first matching case, with an optional default value if nothing else is met.
SWITCH(expression; case1; value1; [case2_or_default; …]; [value2; …])


You can use following formula for "In Progress" column


It will make only "Done" tick box active and "In Progress" depending on it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.