Formula for parent, child, grandchild auto add rows

I am building a spreadsheet for our new hire onboarding and IT checklists. When I add a new name I would like it to auto populate children underneath it. See my screenshots below.

Row 7 (Parent) - Dark blue would be the start date for new employees

Row 8 (Child) - Light blue would be the individual employees (we normally do 10-20 at a time)

Row 9-28 (grandchildren) - When we add a new hire (row 8) I would love to "auto add" these rows. Is this possible?


Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @Mike TV Yes. There is a way to have new rows added using two forms but it does not indent automatically. It simply creates new rows with similar data points. But I don't think that is going to be the most efficient solution in this instance.


    In this particular case, it looks like we are creating the same exact set of rows each time a new hire is added. Using the two form method would require submitting a form to generate every single row every single time. Bulk creating the same set of rows would involve setting up another sheet that contains each of the additional rows. We would then use a trigger on this new sheet to drive a Copy Row automation that will grab all of those rows and copy them over to the working sheet.


    In the end though, it would actually be easier to just copy/paste from the working sheet to populate the new rows.


    The indentations are definitely still going to be manual regardless of how the new rows are added.

    thinkspi.com

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭

    Possibly @Andrée Starå may have a solution for that with his 2 forms method.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Adding the rows themselves is relatively straightforward and wouldn't even require additional forms. The only part that is definitely not possible is automatically indenting them. You would still need to manually do that portion for each entry.

    thinkspi.com

  • Mike TV
    Mike TV ✭✭✭✭✭

    @Paul Newcome

    I thought that @Andrée Starå had a solution using 2 forms (or 1) to create child rows automatically? At least I've seen him post that response in quite a few threads revolving around automatically creating child rows. However, I haven't yet seen him say it was possible to automatically create child of child rows...

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @Mike TV Yes. There is a way to have new rows added using two forms but it does not indent automatically. It simply creates new rows with similar data points. But I don't think that is going to be the most efficient solution in this instance.


    In this particular case, it looks like we are creating the same exact set of rows each time a new hire is added. Using the two form method would require submitting a form to generate every single row every single time. Bulk creating the same set of rows would involve setting up another sheet that contains each of the additional rows. We would then use a trigger on this new sheet to drive a Copy Row automation that will grab all of those rows and copy them over to the working sheet.


    In the end though, it would actually be easier to just copy/paste from the working sheet to populate the new rows.


    The indentations are definitely still going to be manual regardless of how the new rows are added.

    thinkspi.com

  • @Paul Newcome At the moment I am copy/paste the rows and that will work fine. I was curious if we could use a formula to automate that. No worries.

    Thanks for answering!

  • Mike TV
    Mike TV ✭✭✭✭✭

    @Paul Newcome

    That's crazy. I'm 100% certain the topics of conversation were regarding automatically creating child rows and he said it could be done with his 2 form method.

    https://community.smartsheet.com/discussion/76300/nesting-child-rows-automatically-from-a-form-submission

    Am I reading these threads incorrectly?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Mike TV

    I hope you're well and safe!

    What is crazy?

    I've never said that my solution created the Parent and Child relationship, just that it creates the differentiation between them. It's a workaround. You'd need to change the hierarchy yourself if that's required.

    And I'm actually working on/developing a solution to create the Parent/Child hierarchy structure.

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • @Andrée Starå - Thanks for this. How can we be updated about this solution that you are developing?