Uncheck the previous checkbox when a new box is checked

Mellba

Hello

How do I get rid of a previous check when a new checkbox is selected?

 

This is what really need to happen: (Task Name 6 needs to transition from one to another (Idea, Concept, Design, Validation, Lanuch) as the approvals are sent out.

            When project starts it is automatically in the Idea Phase

            When Idea Phase Gate Approval is sent-out – It then changes to Concept Phase

            When Concept Phase Gate Approval is sent-out – It then changes to Design Phase

            When Design Phase Gate Approval is sent-out – It then changes to Validation Phase

            When Validation Phase Gate Approval is sent-out – It then changes to Launch Phase

I have attached a screen shot for review.

Thank you.

 

p_bureau

Hello,

 

a formula won't do what your are looking for! This is because you want something to trigger when a change occurs (not from the value itself).

 

Maybe what you are looking for is Kanban management?

 

Paul.

Chris McKay

Hi Melba,

Not to tread on Paul's toes here, but if you have a Business or Enterprise license, I would consider using Approval Requests and a horizontal array of checkboxes. This will allow you to obtain formalised Stage Gate approval via email (that opens approvals inside a form).

As you can see in the below screenshots, I created a new sheet with each line representing a new project. There are some checkbox columns in the middle, with the Task Name and Phase columns formatted as Text/Numbers and the final Approval column formatted as a Dropdown with the following values:

• Submitted
• Pending Idea Approval
• Pending Concept Approval
• Pending Design Approval
• Pending Validation Approval
• Pending Launch Approval
• Approved
• Declined

The cell formulas are as follows:

• Phase =IF(LEN([Task Name]1) = 0, "", IF(Approval1 = "Pending Idea Approval", "Submitted", IF(Approval1 = "Pending Concept Approval", "Idea", IF(Approval1 = "Pending Design Approval", "Concept", IF(Approval1 = "Pending Validation Approval", "Design", IF(Approval1 = "Pending Launch Approval", "Validation", IF(Approval1 = "Launched", "Launch", "Submitted")))))))
• Idea =IF(OR(Phase1 = "Idea", Phase1 = "Concept", Phase1 = "Design", Phase1 = "Validation", Phase1 = "Launch"), 1, 0)
• Concept =IF(OR(Phase1 = "Concept", Phase1 = "Design", Phase1 = "Validation", Phase1 = "Launch"), 1, 0)
• Design =IF(OR(Phase1 = "Design", Phase1 = "Validation", Phase1 = "Launch"), 1, 0)
• Validation =IF(OR(Phase1 = "Validation", Phase1 = "Launch"), 1, 0)
• Launch =IF(Phase1 = "Launch", 1, 0)

Now we have that all set up, you can click on the Alerts & Actions button at the bottom of the screen. I've only setup the first few automation steps, but the images should give you an idea of how they work.

You essentially want to set the Submitted, Approved and Declined values along one step for each new Approval Request. Eventually, the last step will have:

• Pending Launch Approval
• Launched
• Declined

If the final Launch approval is given, the Approval column will show Launched, The phase will show Launch and all the checkboxes will be selected.

You can change these values to whatever suits your requirements. Selecting Delivery Settings allows you to change when Approval Requests are sent (e.g. once per day), what columns are included, a subject, custom message etc.

When you've designed an Approval Request that works for you, clone it using the dropdown on the Approval Request screen and you can complete your approval workflow. You could also add some conditional formatting to the checkboxes to give viewers a nice visual experience.

Hope this gives you some ideas .