Uncheck the previous checkbox when a new box is checked
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.
Comments
-
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.
-
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 .
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!