Updating Project Stage Advancement formula

Jenni N

We have a current formula for Project Stage Advancement, that updates the Project Stage field when all tasks under in the section have been marked 100% and a task in the next section has been marked In Progress.

=IFERROR(INDEX([Task Name]:[Task Name], MATCH(MAX(COLLECT([Start Date]:[Start Date], Status:Status, OR(@cell = "In Progress", @cell = "Complete"), Hierarchy:Hierarchy, =2)), [Stage Helper]:[Stage Helper], 0)), "")

We would like to update our process and formula from checking if every task is complete in the stage to check if the [Advancement Task] column =1 and if that task is 100% complete. The [Advancement Task]=1 and being 100% complete would start the next Project Stage.

Current Process:

I've tried to update the formula, but all of my attempts have lead to Invalid Argument or Unparsable results. Any help with a new formula would be apprecitated.

Hamza1

Hi @Jenni N,

To update your process and formula from checking if every task is complete in the stage to checking if the [Advancement Task] column =1 and if that task is 100% complete you can try the following: 

• You’d want to add a helper criteria into your Advancement Task formula that looks to see if the current row is a Level 2 hierarchy (Grey)
• If it is, then check to see if the Advancement Task with a 1 is at 100%.
• If it is, return the text “Advancement Complete”
• Then in your original formula you can add [Advancement Task]:[Advancement Task], "Advancement Complete" as a criteria.

 The formula would be as follows:

=IFERROR(INDEX([Task Name]:[Task Name], MATCH(MAX(COLLECT([Start date]:[Start date], Status:Status, OR(@cell = "In Progress", @cell = "Complete"), Hierarchy:Hierarchy, =2, [Advancement Task]:[Advancement Task], "Advancement Complete")), [Stage Helper]:[Stage Helper], 0)), "")

 See the following screenshots of the formula for reference:

 

Cheers,

Hamza

Hamza1

Hi @Jenni N,

To update your process and formula from checking if every task is complete in the stage to checking if the [Advancement Task] column =1 and if that task is 100% complete you can try the following: 

• You’d want to add a helper criteria into your Advancement Task formula that looks to see if the current row is a Level 2 hierarchy (Grey)
• If it is, then check to see if the Advancement Task with a 1 is at 100%.
• If it is, return the text “Advancement Complete”
• Then in your original formula you can add [Advancement Task]:[Advancement Task], "Advancement Complete" as a criteria.

 The formula would be as follows:

=IFERROR(INDEX([Task Name]:[Task Name], MATCH(MAX(COLLECT([Start date]:[Start date], Status:Status, OR(@cell = "In Progress", @cell = "Complete"), Hierarchy:Hierarchy, =2, [Advancement Task]:[Advancement Task], "Advancement Complete")), [Stage Helper]:[Stage Helper], 0)), "")

 See the following screenshots of the formula for reference:

 

Cheers,

Hamza

Jenni N

@Hamza1, thank you so much for taking the time to not only respond to my request but also with screenshots. I appreciate your help.

This has me so close to the outcome I am looking for. I need to project stage to advance to the next Hierarchy 2 when the next checked task is greater than 0% (the status is "In Progress"). i I updated the Advancement task formula to take that into consideration, but Project Stage won't update.

For example, the project stage is reporting as:

However, I would expect it to be reporting 07- Design Development because the first task in that section is In Progress.

Jenni N

I figured it out, changing the checked task under 07-Design Development to just 1% wasn't a high enough percentage to get the hierarchy 2 row to change to "In Progress". Once I changed the checked task to 10% the Project Stage updated to 07-Design Development as expected.