nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Smartsheet Community

Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.

Move project rows if all descendant rows are complete

StephanieD

Hello!

I'm fairly new to Smartsheet and I'm working to set up an automation to move project rows to an archived sheet once everything for the project is complete. I'm hoping someone can help me out with a specific formula.

I have tasks and subtasks under my projects. I want to verify that all project items are complete before archiving in case of a project accidentally getting marked complete. I have a Status column, and each item (project (highest level), tasks, and subtasks) can be marked complete.

I created a Ready to Archive checkbox column. I'm hoping to use this as the trigger to move the rows, but I can't get this column to correctly identify what is complete and not.

I think I may need to use a formula for descendants vs parent/child. I understand I also may need a helper column. I've been searching all the articles and references and tried many things but haven't been able to get anything to work.

Any assistance is appreciated.

Edited to add: Below is a screenshot. You can see the last formula I tried using is marking all the checkboxes in the Ready to Archive column whether those items are complete or not.

Find more posts tagged with

Sheets

Formulas

Accepted answers

All comments

Andrée Starå

https://community.smartsheet.com/discussion/148052/move-project-rows-if-all-descendant-rows-are-complete

Hi,

I hope you're well and safe!

Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

I hope that helps!

Have a fantastic day!

Andrée Starå | Smartsheet Expert Consultant and Partner / CEO @ WORK BOLD

✅ Did my comment/answer help? Please support with 💡 ⬆️ ❤️ ✅ so others find it. Thank you! 🙏

StephanieD

I edited my original post to include the screenshot.

You can see my Ready to Archive column is currently marking everything even if the row isn't marked complete in the Status column.

Paul Newcome

Try something like this:

=IF(COUNTIFS(DESCENDANTS(Status@row), @cell <> "Complete") = 0, 1)

StephanieD

Thank you Paul.

I did try that formula, and it fixed some of the cells, but not everything is calculating correctly. I did update the column formula.

Below is and updated screenshot.

Paul Newcome

Which row or rows are not calculting correctly?

StephanieD

My understanding is that the formula in the Ready to Archive column will only mark the checkbox if an item is marked complete. Rows 26, 27, 28, 31, 32, & 49 are all marked in the Ready to Archive column, but the status is not set to Complete. And row 54 does have a status of Complete, but does not have a checkmark in the Ready to Archive column.
I used this formula:

=IF(COUNTIFS(DESCENDANTS(Status@row ), @cell <> "Complete") = 0, 1)

I could be completely misunderstanding how this formula works, so please provide clarity if I'm not understanding correctly.

StephanieD

Thank you, Paul!
That formula got me closer to where I want to be.

This is the formula I currently have:

=IF(AND(COUNT(ANCESTORS()) = 0, Status@row = "Complete", COUNTIF(DESCENDANTS(Status@row), @cell <> "Complete") = 0), 1, 0)

I thought it was working, but I found an instance where it doesn't work. If I have a project and tasks and then later in tasks, I have a task that has subtasks, the Ready to Archive column will still mark if the task with subtasks are not marked complete.

Now I tested with marking the whole project complete, which I understand we wouldn't do if tasks and subtasks are not complete, but I was testing to ensure if there was an error it still wouldn't work.

In my screenshot you can see the last task and subtask were not marked complete and still the Ready to Archive column was checked based on my formula and the whole project moved.

I'd love to know if anyone has a fix for this?

Paul Newcome

Did my formula not work?

StephanieD

No, it didn't work as I was hoping it would. But it was still helpful in getting me closer to my end goal, so thank you for that.

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, category/bannerUrl, category/bannerImageAlt, category/extensions, category/customizationLabels, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/bannerImageAlt, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.