Copying to Children Rows while Editing Parents - Best Practice Question

Hello!

I have a large master sheet (900 rows) with ancestor levels of 0-4. Whatever is in ancestor level 1 in columns 1-12, I need that to copy down to ancestor 2 for all of the children of that parent. I need this to happen throughout the sheet. But I need the information to be fillable in ancestor 1 (not locked up with the column formula, as can happen when viewing from reports).

I'm thinking I could set this up with helper columns, but I just wanted to ask here if there were any other ways to do this besides setting up 12 columns which I can edit and another 12 helper columns to view things on every row and taking up more space in my sheet (we can't hide these - we need to edit and view from every row).

My current workaround is just manually adding =Parent() to each of the ancestor 2 children, but it's easy to overlook things, and we add new children often. It would be better if we could have something set to a column formula just to make sure we don't miss these important notes in our reports.

Or maybe an automation would be better… if that could check for the ancestor 1 data and fill it to all the children when it's updated, that could work…. but not sure about limitations or future-proofing here.

Appreciate any thoughts/feedback!

Find more posts tagged with

Formulas

Automations

Accepted answers

All comments

Mark.poole

@S.Stone Honestly you can set it up with 1 helper column that you edit and a formula that accounts for each of the children using count(ancestor()).

ex.

=IF(COUNT(Ancestors()) = 1, Helper@row , IF(COUNT(ANCESTORS()) = 2, PARENT()))

so on and so forth.

S.Stone

@Mark.poole do you mean just combining everything into one "read" column?

If so, that works in general terms of Smartsheet, but we're tracking a lot of different pieces of information that we need to keep separate. In the original 12 columns, we have dropdowns to track different categories of information and updating different text fields and dates. If we just bunched everything together into one "read" column, you wouldn't be able to digest the information easily.

My current =Parent() just copies down so each of the 12 columns remain separated so you can see all of the information, including dates that correspond to their column headers.

Let me know if I've misunderstood, though. But if this is what you're proposing, this solution, while totally valid, wouldn't be ideal in our specific case.

Mark.poole

@S.Stone no. that is actually a misunderstanding on my part. Now that I understand better. My suggestion would indeed require multiple helper columns. The only option i can see is creating a click and drag formula.

S.Stone

@Mark.poole Thanks, yeah, that was my first instinct. I'm not sure if automation would end up overwriting the wrong things - it's tricky for me to get those automations just right, and it's much harder to revert any of those changes in my experience.

I'll leave the post up in case anyone else has any ideas… I'll keep pondering myself, too. Sometimes just writing out a problem can lead to solutions, after all. :)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.