Creating report to show all Parent Row based off Child Row

SystemsWiz

I'm trying to create a report to display all tasks assigned to logged user (current user option).

I want to see all the parent rows for all children, i.e. if current user is Jane, I want all the children tasks showing but also want to see the parent task it is related to.

So, in report, if Jane logs in for task 2.1.2 I would like to see parent 2.1 F, 2.0 C and A.

I have many small projects like these so this is example of just one of them.

Another question is, I want the parent row to update to complete (green) automatically when children are green, how do I do that?

Thanks in advance for the help.

Attached the template of my smartsheet as an excel

Example template - Copy.xlsx

MedaUser

Hi,

I've done some playing around with your sheet and there are some limitations to the answers for each of your questions that I was able to determine.

1. All parent tasks to display on report - Unfortunately, this doesn't seem possible since there isn't a formula that exists to output 1:1 text for data higher than the Parent, so if a task exists lower than 1 level (i.e. task 2.1.1 G), then I'm unable to display task 2.0E. I'll attach my sheet to show you how I suggest pulling the data of the parent if you are determined it exist in a report. Otherwise, your filters within that sheet are the best outcome for what you're trying to do (I recognize this doesn't help when collaborating cross sheets)
2. Change status color at parent level - This one is difficult since the Automation to change cell values is limited and doesn't include Symbol columns yet. Therefore, I wasn't able to figure this one out without making the formulas unique to the parent row cells, which means they can be overwritten easily.

Sorry I couldn't be more helpful! 😔

Example template - Copy.xlsx

MedaUser

Sorry, but I have an update on the Parent Task column...use this formula instead and it should resolve #1 above for you:

=JOIN(ANCESTORS(Primary@row), " > ")