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

Stephanie Zeoli

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.

https://community.smartsheet.com/discussion/75444/automatic-project-phase-formula

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!

Paul Newcome

You are probably going to need an INDEX/COLLECT instead of an INDEX/MATCH. It will most likely end up looking something like this...

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

James Keuning

I would create another column and put the formula in there. Something like:

IF(LEVEL>2,YourFormula,"")

Stephanie Zeoli

That didn't work, it give me an invalid operation. The formula is actually nested in a field in the sheet summary. It returns #INVALID OPERATION. Thank you though.

Paul Newcome

You are probably going to need an INDEX/COLLECT instead of an INDEX/MATCH. It will most likely end up looking something like this...

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

Stephanie Zeoli

Thank you Paul,

I think we are getting closer, but that didn't work either. I will work with your suggestion though. I appreciate the help!

Paul Newcome

Are you getting the wrong value or are you getting an error?

Stephanie Zeoli

I am getting "INVALID VALUE"

Paul Newcome

Ok. How exactly are you entering the % Complete?

Stephanie Zeoli

You mean the field % Complete? Manually input, that is the main user controlled field.

Paul Newcome

Ok. Are the percentages being entered as numbers into a column that is formatted for percentages or are you manually entering in the "%"?

Stephanie Zeoli

It is part of the "Project Settings" that is already formatted as a percentage complete column. User just enters number 0-100.

Paul Newcome

Hmm... How exactly is the Level column populated then?

Stephanie Zeoli

Level has the formula below in the column:

=COUNT(ANCESTORS([Task Name]@row))

Is that what is throwing it off?

Leibel S

@Stephanie Zeoli

You are getting this error because you don't have any tasks that meet this criteria. Your tasks are either 100% or 0%.

To work around this you would have to decide what you want it to show in this case.

Stephanie Zeoli

Wow! I've definitely been staring at the screen too long. Thank you @Leibel Shuchat

Huge thanks @Paul Newcome. I appreciate your help!

Problem solved.

Paul Newcome

@Leibel Shuchat Good eye. Thanks for catching that. I don't often look at the details of the data like that when I can't see the entire sheet until everything else has been exhausted.