Help on formula: Return value

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

Progress, State.png

Find more posts tagged with

Formulas

Solution Center

Comments

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

Stage 2.png

Mike Wilday

Glad I could help you out! Enjoy!

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?