Formula for parent, child, grandchild auto add rows

Blake

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?

Paul Newcome

@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.

Mike TV

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

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.

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

@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.

Blake

@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

@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/75905/form-to-create-child-rows

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

Am I reading these threads incorrectly?

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!

Blake

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

Warren Labuschagne

https://community.smartsheet.com/discussion/comment/330318#Comment_330318

@Andrée Starå the ability to automatically indent a new row would be a great feature. Especially if you are moving/copying rows (that were indented) to a new sheet. Is there any way of totalling (SUM) the value of all rows in a column and including new rows as the are added from another sheet? I have used the formula =SUM(Amount2:Amount50), but am concerned if more than 50 rows are added, it won't count these in.

Paul Newcome

@Warren Labuschagne Have you checked out the new Ideas topic (the new ay of submitting a product enhancement request)? Someone may have already suggested this which means you can now vote for it. If no one has then you can create the new idea yourself which allows others to vote on it.

As for your specific use case... You can use hidden helper columns on the original sheet to pull the parent row data onto every child row. From there your formulas can reference entire columns (which grabs new rows as they are added) and include range/criteria sets to help filter.

Basic summing though with the SUM function, you should be able to just reference the entire column and have the new rowss grabbed as soon as they are added regardless of hierarchy.

Warren Labuschagne

https://community.smartsheet.com/discussion/comment/337158#Comment_337158

@Paul Newcome thanks very much for the help, I didn't realise you could Sum the entire Column, this solved the issue, but I will still request a product enhancement request as it would be great for the hierarchy to be copied for other reasons.

NikkiOno

@Andrée Starå

In the response above, you stated that you were working on a solution related to parent and children rows. Could you please let me know the status as I need to do the same thing as Blake who originally posted the question?

sandra.love

@Paul Newcome

In reference to this comment from above:

"As for your specific use case... You can use hidden helper columns on the original sheet to pull the parent row data onto every child row. From there your formulas can reference entire columns (which grab new rows as they are added) and include range/criteria sets to help filter."

How would I go about that? when I have tried this I get a circular error. Say I enter 5 rows (using Andre's two-form system). I have 10 columns, 5 of them would be the same information for all 5 rows (one of those columns is PO number) the other 5 column's info will vary by row (think item number and color etc). How can I use a formula in the method you have referenced above to copy the duplicative info down from the first rows to the remaining 4 rows where that info is blank? I hope this makes sense

Genevieve P.

Hi @sandra.love

In the example mention above, the formula uses hierarchy to populate data down rows.

=PARENT()

However in your case, the rows are all on the same level so you wouldn't be able to use a formula like this. You could use a change cell workflow to update cells to always have a static value, or copy data from one sheet into this sheet using INDEX(MATCH.

Paul has another solution post here, that may help: Auto-Create Child Rows WITHOUT Bridge or API

Cheers,

Genevieve