# 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.

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!

• ✭✭✭✭✭✭

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)

• ✭✭✭✭✭

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

IF(LEVEL>2,YourFormula,"")

• 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.

• ✭✭✭✭✭✭

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)

• 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!

• ✭✭✭✭✭✭

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

• I am getting "INVALID VALUE"

• ✭✭✭✭✭✭

Ok. How exactly are you entering the % Complete?

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

• ✭✭✭✭✭✭

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

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

• ✭✭✭✭✭✭

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

• Level has the formula below in the column:

Is that what is throwing it off?

• ✭✭✭✭✭✭

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.

• 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.

• ✭✭✭✭✭✭

@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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!