How to write formula to pull current active phase or stage of the project with specific levels?


I found a formula on another post that allows me to pull the most current or active stage of the project with the least percentage done, higher than 0% and lower than 99%, but am only wanting to pull the task below the Phase and sub-phase/stage. I've created a helper column for levels and need to only index the task that are level greater than 2.

The formula above works well, however I am needing to only pull phases with a level greater than 2. I keep trying to insert that criteria in the above formula and it returns errors.

I am using formula below:

=INDEX([task name]:[task name], MATCH(MAX(COLLECT([% Complete]:[% Complete], [% Complete]:[% Complete], AND(@cell < 0.99, @cell > 0))), [% Complete]:[% Complete], 0))

Is anyone able to offer any suggestions? Thank you!

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!