Help on formula: Return value

Grace

Hi all,

I am building a project tracker however I am new to creating formulas so please need your help again.

Based on the image below, I wanted to get the Stage and the Progress of the project that will later complete my summary report by just displaying the main headers in yellow.

On row 1, Stage column, I want to get the current task that has an Active status. So if the status of any of the sub tasks is "Active", what is the current task.

The stages or the tasks of the project are the ones just below the project name and are sequential so only one of the stages can have the Active status.

In the image below, the task that has the Active status is "Planning" and is the expected text to display on row 1 Stage column.

The Progress column might have the same formula as above but here's what I wanted to get; if the status is Active, what is the current Progress.

Not sure if I could just drag the formulas in since there would be multiple projects of the same format and new ones could be added in at any point, is this possible?

Kind regards,

Grace

Mike Wilday

Hi Grace, 

We did something similar in our reports, but what we had to do was create a massive nested IF statement that checked each row of the children task to give us a status. It would look something like this... 

=IF(stage2 = "Active", Progress2, IF(stage3 = "Active", Progress3, IF(stage4 = "Active", Progress4, IF(stage4 = "Active", Progress4, IF(stage5 = "Active", Progress5, IF(stage6 = "Active", Progress6))))))

How this works... it looks at the earliest stage that is set to Active and presents that stage's progress. If you have 2 stages in active state, only the first one it finds will be reported on. Not sure if that is what you are looking for or not... You could reverse the order of the IF statement to look for the latest stage first, so that only the furthest one is being reported on. So if something was active and delayed, but the next stage was already active, that it would report that instead. Whichever criteria is met first in the IF statement will fire. 

Hope that helps! 

Grace

Hi Mike,

You just made my day! I just tweaked the formula a little bit to display the text that I want.

=IF(Status2 = "Active", [Project Name]2, IF(Status3 = "Active", [Project Name]3, IF(Status4 = "Active", [Project Name]4, IF(Status5 = "Active", [Project Name]5, IF(Status6 = "Active", [Project Name]6)))))

What's good about our project planner is that it is sequential and will only have 1 active status per project but if anything changes in the future I will use your advise.

(Btw, don't get confused with the dates on the images below. I just randomly put in some dates to see if the formula works.)

Thank you again for the help!

Best,

Grace

Mike Wilday

Glad I could help you out! Enjoy!