How Do I Return a Status/Name From a Different Sheet within a Project in Sheet Summary

Jenny A.

In the Ancestors column I used:

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

In the sheet summary field I used:

=IF(COUNTIFS(Status:Status, @cell = "On Hold", Ancestors:Ancestors, @cell = 0) > 0, "On Hold", IFERROR(LEFT(INDEX(COLLECT([Task Name]:[Task Name], Status:Status, @cell <> "Complete", Ancestors:Ancestors, @cell = 0), COUNTIFS(Status:Status, @cell <> "Complete", Ancestors:Ancestors, @cell = 0)), FIND("-", INDEX(COLLECT([Task Name]:[Task Name], Status:Status, @cell <> "Complete", Ancestors:Ancestors, @cell = 0), COUNTIFS(Status:Status, @cell <> "Complete", Ancestors:Ancestors, @cell = 0)) - 2), "Complete")

When I add them this way, I get this error in sheet summary. I believe I did this right, so not sure. Thanks

Paul Newcome

Can you provide a screenshot of the second in the sheet summary field open as if you are about to edit it?

Jenny A.

Paul Newcome

I think it is a missed parenthesis. Try this:

=IF(COUNTIFS(Status:Status, @cell = "On Hold", Ancestors:Ancestors, @cell = 0) > 0, "On Hold", IFERROR(LEFT(INDEX(COLLECT([Task Name]:[Task Name], Status:Status, @cell <> "Complete", Ancestors:Ancestors, @cell = 0), COUNTIFS(Status:Status, @cell <> "Complete", Ancestors:Ancestors, @cell = 0)), FIND("-", INDEX(COLLECT([Task Name]:[Task Name], Status:Status, @cell <> "Complete", Ancestors:Ancestors, @cell = 0), COUNTIFS(Status:Status, @cell <> "Complete", Ancestors:Ancestors, @cell = 0))) - 2), "Complete"))

Jenny A.

Thanks! I got it to work. I did update one of the "Complete" words to "In Progress", so then when a phase is In Progress it will show said phase, but if on hold then it will show on hold. Thank you so much again! Appreciate it!

=IF(COUNTIFS(Status:Status, @cell = "On Hold", Ancestors:Ancestors, @cell = 0) > 0, "On Hold", IFERROR(LEFT(INDEX(COLLECT([Task Name]:[Task Name], Status:Status, @cell <> "Complete", Ancestors:Ancestors, @cell = 0), COUNTIFS(Status:Status, @cell <> "Complete", Ancestors:Ancestors, @cell = 0)), FIND("-", INDEX(COLLECT([Task Name]:[Task Name], Status:Status, @cell <> "In Progress", Ancestors:Ancestors, @cell = 0), COUNTIFS(Status:Status, @cell <> "Complete", Ancestors:Ancestors, @cell = 0))) - 2), "Complete"))

Jenny A.

Hi! Nevermind. I thought what I did was working. The one you did looks to work. I guess my next question for this would be if "0" main phase was reopened is there a way to revert it back. So like if we are in Development, but then need to revert it back to Concept is there a way to just make it change? I tried that on a live project, went from Concept to Dev and then back and now it won't recognize Concept even though I changed it back to in progress.

Thanks again!

Paul Newcome

Can you provide screenshots and show me what you are doing that isn't working?

Jenny A.

Formula was put in Sheet Summary as you had it.

I tested marking the Concept Phase as Complete and changed Development to In Progress. Sometimes we have to revert it back a phase because of the changes or updates. When I put changed Development to Not Started and put Concept as In Progress again, it is returning that we are in the Development phase.

If it is not possible to make that change just happen or if it is easier to remove the Ancestors column and add formulas again, then that is what I can do. Thanks

Paul Newcome

You could try this:

=IF(COUNTIFS(Status:Status, @cell = "On Hold", Ancestors:Ancestors, @cell = 0) > 0, "On Hold", IFERROR(LEFT(INDEX(COLLECT([Task Name]:[Task Name], Status:Status, @cell = "In Progress", Ancestors:Ancestors, @cell = 0), 1), FIND("-", INDEX(COLLECT([Task Name]:[Task Name], Status:Status, @cell = "In Progress", Ancestors:Ancestors, @cell = 0), 1)) - 2), "Complete"))

Jenny A.

That worked! Thanks so much! I will be adding this to all 100+ of our projects. Thanks again!

Paul Newcome

Happy to help. 👍️

Jenny A.

Sorry I have one last question as it just came up.

There is one other situation, I am wondering if there is a way to capture in that formula.

Sometimes we leave Concept as In Progress and actually move into Development. So then Development is In Progress. When I do this, it only grabs Concept into sheet summary. Is there a way it could grab the second In Progress if that comes up? Usually we would go from one phase to the next, but this does happen. Just wondering. Thanks so much!

Patrick L.

@Paul Newcome… Sorry to comment here but would you be able to help with some smartsheet activites that i am having trouble figuring out? If you want to reach out at Leeroy2082@gmail.com i would greatly appreciate it. I dont know another way to contact you on here. Sorry @Jenny A. im not trying to hijack your thread. have a wonderful day.