Skip to content
  • Try Smartsheet

    • Smartsheet Basics

    Smartsheet Basics

    Ask questions about the core Smartsheet application: Sheets, Forms, Reports, Dashboards, and more.

    Creating report to show all Parent Row based off Child Row

    ✭✭
    in Smartsheet Basics

    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.

    SS - question.JPG

    Attached the template of my smartsheet as an excel

    Excel
    Example template - Copy.xlsx
    Uploaded 10.85 KB


    · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭✭

      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! 😔

      Excel
      Example template - Copy.xlsx
      Uploaded 11.19 KB

      Travis C, PMP

      Smartsheet Leader with 5+ years of SS experience

      Let's connect: LinkedIn - Travis C.

      If my answer was sufficient, pleaseupvote and mark my response as answered.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      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), " > ")

      Travis C, PMP

      Smartsheet Leader with 5+ years of SS experience

      Let's connect: LinkedIn - Travis C.

      If my answer was sufficient, pleaseupvote and mark my response as answered.

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Quick Links

    Categories

    Trending in Smartsheet Basics

    Help shape the future of Smartsheet.
    Share your ideas and feature requests.

     Watch video