Help on formula: Nested ifs

Grace

Hi,

Please need your help on the below formula.

As you can see on the image below, the stage column is set up as a dropdown list. I wanted to get a formula that says, if the project owner decided to close the project at a certain stage (these are the subtasks) because of any of the listed reasons, it will be reflected in the Stage column highlighted in yellow.

I tried to add the text in bold to see if anything will work but nothing happend.

=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, IF(Status2 = "Not Started", "Awaiting Kick-off", IF(Status3 = "Not Started", [Project Name]3, IF(Status4 = "Not Started", [Project Name]4, IF(Status5 = "Not Started", [Project Name]5, IF(Status6 = "Not Started", [Project Name]6, IF(Status6 = "Completed", "Closed", IF([Project Name]2 = "Planning", Stage2))))))))))))

 

Kind regards,

Grace

Mike Wilday

It might be because one of your other IF statements is true and firing before it gets to that point. Try moving the bold part up earlier in the list. 

L_123

I don't know what is exactly wrong with your formula, but I if I understand what you want the output to be you are doing this a more difficult way. Try the formula below and see if it has the results you want

=JOIN(COLLECT(CHILDREN([Project Name]@row), CHILDREN(Stage@row), FIND("Closed", @cell) > 0), " ")

 

Grace

Hi both,

Thank you for the response. I have tried both of your suggestions however it's not displaying the expected result.

Kind regards,

Grace

Mike Wilday

Hi Grace, from a closer look at your original formula, I have underlined the part that is firing. 

=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, IF(Status2 = "Not Started", "Awaiting Kick-off", IF(Status3 = "Not Started", [Project Name]3, IF(Status4 = "Not Started", [Project Name]4, IF(Status5 = "Not Started", [Project Name]5, IF(Status6 = "Not Started", [Project Name]6, IF(Status6 = "Completed", "Closed", IF([Project Name]2 = "Planning", Stage2))))))))))))

That is why you're planning is not showing up. It checks each criterion - one at at time - till one passes then stops checking if one passes. You are getting the result of Tender because its seeing that status3 is "Not Started" then it fires. 

Grace

Hi Mike,

I tried the below but I still can't seem to make it right. 

=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, IF([Project Name]2 = "Planning", Stage2, IF([Project Name]3 = "Strategy", Stage3, IF([Project Name]4 = "Tender", Stage4, IF([Project Name]5 = "Evaluation", Stage5, [Project Name]4, IF([Project Name]6 = "Contract", Stage6, IF(Status2 = "Not Started", "Awaiting Kick-off", IF(Status3 = "Not Started", [Project Name]3, IF(Status4 = "Not Started", [Project Name]4, IF(Status5 = "Not Started", [Project Name]5, IF(Status6 = "Not Started", [Project Name]6, IF(Status6 = "Completed", "Closed"))))))))))))))))

This is my original formula for the Stage column: =IF(Status26 = "Active", [Project Name]26, IF(Status27 = "Active", [Project Name]27, IF(Status28 = "Active", [Project Name]28, IF(Status29 = "Active", [Project Name]29, IF(Status30 = "Active", [Project Name]30, IF(Status26 = "Not Started", "Awaiting Kick-off", IF(Status27 = "Not Started", [Project Name]27, IF(Status28 = "Not Started", [Project Name]28, IF(Status29 = "Not Started", [Project Name]29, IF(Status30 = "Not Started", [Project Name]30, IF(Status30 = "Completed", "Closed")))))))))))

This works fine but now I wanted to add a formula in there that says if the project owner selects "Closed - Extension" in any of the cells in the Stage column then that same text should appear on the header.

Another thing is that if the Stage header for a project becomes "Closed - Extension" then the entire row should be in green. I have an existing conditional formatting for projects that closed completely but not sure about the right formatting to apply without highlighting the subtasks.

Kind regards,

Grace

Mike Wilday

Well, based on your first column... and your adjusted formula, you are only checking row 6 for closed. If you want to check each of the sub rows you need to add them to your if statement. Try this one: 

=IF(Stage2 = "Closed - Extension", "Closed - Extension", IF(Stage3 = "Closed - Extension", "Closed - Extension", IF(Stage4 = "Closed - Extension", "Closed - Extension", IF(Stage5 = "Closed - Extension", "Closed - Extension", IF(Stage6 = "Closed - Extension", "Closed - Extension", 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, IF([Project Name]2 = "Planning", Stage2, IF([Project Name]3 = "Strategy", Stage3, IF([Project Name]4 = "Tender", Stage4, IF([Project Name]5 = "Evaluation", Stage5, IF([Project Name]6 = "Contract", Stage6, IF(Status2 = "Not Started", "Awaiting Kick-off", IF(Status3 = "Not Started", [Project Name]3, IF(Status4 = "Not Started", [Project Name]4, IF(Status5 = "Not Started", [Project Name]5, IF(Status6 = "Not Started", [Project Name]6, IF(Status2 = "Completed", "Closed", IF(Status3 = "Completed", "Closed", IF(Status4 = "Completed", "Closed", IF(Status5 = "Completed", "Closed", IF(Status6 = "Completed", "Closed")))))))))))))))))))))))))

That will check each of the stage rows for Completed Extension - and display that in the header... then if one of those rows don't have "Closed - Extension" it will check for any active row and display the project name, if none of those are true it will then check row 2's project name and if it says "Planning" then it will display planning.. and so forth for each of the sub-rows... You might have an issue at this point because based on your template the project name in row 2 will always display planning. (I italicized the part that might be tripping you up and keeping your closed status from appearing. If you remove the IF([Project Name] parts that display the project names then you should see the closed statements. Not sure how to direct you on this part, but I think you would want to check for the first active stage and if it's active then, display that row's project name. Instead of checking for the project name, because the status will change, but the project name won't. Does that make sense? 

 

Grace

Hi Mike,

I removed the project name part and I am able to get the results now using the formula below. Thank you for the help.

=IF(Stage2 = "Closed - Extension", "Closed - Extension", IF(Stage3 = "Closed - Extension", "Closed - Extension", IF(Stage4 = "Closed - Extension", "Closed - Extension", IF(Stage5 = "Closed - Extension", "Closed - Extension", IF(Stage6 = "Closed - Extension", "Closed - Extension", 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, IF(Status2 = "Not Started", "Awaiting Kick-off", IF(Status3 = "Not Started", [Project Name]3, IF(Status4 = "Not Started", [Project Name]4, IF(Status5 = "Not Started", [Project Name]5, IF(Status6 = "Not Started", [Project Name]6, IF(Status6 = "Completed", "Closed"))))))))))))))))

With regards to the conditional formatting, do you have an idea how I can display the headers in green when the Stage is in either "Closed" or "Closed - Extesion" without turning the subtasks row into green too?

Kind regards,

Grace

Mike Wilday

Yes, you can create a conditional formatting with two conditions and make sure you apply it to the entire row. 

After you set your first condition, click on the drop-down arrow and add another condition. Your first condition should be that your REF# field is not blank. Check out my animated gif to see how to do it. 

You can right-click on the image and choose open in new tab to see it large.

Grace

Thanks a lot Mike! I got it working now

Best,

Grace

Mike Wilday

Glad I could help you out.