Feedback Needed on Status Rollup Solution

Hi all,

I have a bunch of underlying sheets that have the exact same setup due to a template and it has a status field with a drop-down of about half a dozen different options that go sequential order from not started to complete and verified as complete from leadership.

We also have hierarchy so there is level 0, 1 and 2 that we managed to indentations so we can collapse all of the underlying work. We would like level 1 and level 0 to automatically roll up from level two on what the status is instead of it having to be manually plugged in. When we try to insert a column formula, it does not work because the drop-down will disappear. We do not want to manually put formulas in specific cells and we want to be dynamic to reduce the overhead for the team at scale. This is key so manual patch in specific cells is not gonna work for us.

Any feedback or thoughts on how to manage / implement a solution for this?

Find more posts tagged with

Formulas

functionality

Dropdown

best practices

executive reporting

Accepted answers

All comments

Paul Newcome

You could set up some change cell automations in conjunction with a hidden helper column with column formula. Basically the hidden helper column would have whatever formula you used to output the appropriate status on the parent rows, and then you would use a series of change cell automations to change the main status column on parent rows in accordance with whatever is output by the formula.

Aakash S

So if I understand correctly:
First, we would have helper cells roll up the status for the parents.
Second, the automaton will track if the status on helper is different than actual status for L0 and L1. If yes, we replace the text in the status field to match what the helper says using a sheet automaton.

I will give this a try this week and see if this works as intended.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.