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?
Best 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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Possibly @Andrée Starå may have a solution for that with his 2 forms method.
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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...
-
@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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@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!
-
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.
Am I reading these threads incorrectly?
-
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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Andrée Starå - Thanks for this. How can we be updated about this solution that you are developing?
-
@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.
-
@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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@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.
-
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?
-
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
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!