从女儿ro状态更新ws

Hi Smartsheets Team,

I have a column "Status" that has mother and daughter rows.

I am wanting the mother "Status" to update when the daughter rows change.

Screen Shot 2023-07-19 at 8.35.08 AM.png

So for example APQP would be "Not Started" until Pre APQP or MFG Plan Review is marked as "In Progress" then APQP becomes "In Progress". When BOTH are marked complete the APQP lines becomes "Complete".


Can you help me please?

Tags:

Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭
    Answer ✓

    You will have to modify the name for [Primary Column] in the formula to match your sheet, but this should work.

    =IF(CONTAINS("In Progress", CHILDREN()), "In Progress", IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN([Primary Column]@row)), "Complete", "Not Started"))

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭
    Answer ✓

    You will have to modify the name for [Primary Column] in the formula to match your sheet, but this should work.

    =IF(CONTAINS("In Progress", CHILDREN()), "In Progress", IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN([Primary Column]@row)), "Complete", "Not Started"))

  • Wow thank you for your quick response - it works perfectly!! THANK YOU!

  • @Carson Penticuff

    Could you show a modified the formula, with the same idea as original poster but where children drop drop option are:

    Complete or N/A or Canceled = Complete

    Progressing or Pending = Processing

    Not Started = Not Started

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭

    @Christopher沃伦

    I'm not sure I understand that logic entirely. What about situations where children have a combination of Complete and Not Started, or Complete and Progressing, or a combination of all three?

  • Christopher Warren
    edited 07/19/23
    Screenshot 2023-07-19 131808.jpg

    @Carson Penticuff

    Children only one drop down value at a time.

    For example, Parent row (Real Estate) would be "Not Started" until one of the Children Rows (Pro Forma, LOI, PSA, etc) are marked as "Processing", "Pending", "Completed", "N/A" or "Canceled" then Parent row (Real Estate) would become "Processing". When all children are marked "Completed" or "N/A" or "Canceled" the Parent row is changed to "Completed".

    I have this thus far, but can't figure out when all children are either Complete &/or N/A &/or Canceled but one is Not Started? The parent shows Not Started.

    =IF(CONTAINS("Processing", CHILDREN()), "Processing", IF(CONTAINS("Pending", CHILDREN()), "Processing", IF(CONTAINS("Completed", CHILDREN()), "Processing", IF(COUNTIF(CHILDREN(), OR(@cell = "Completed", @cell = "N/A", @cell = "Canceled")) = COUNT(CHILDREN([email protected])),“完成”,“没有开始”))))


    If I use the following formula, even when all are Complete, N/A or Canceled, Parent still says Processing:

    =IF(CONTAINS("Processing", CHILDREN()), "Processing", IF(CONTAINS("Pending", CHILDREN()), "Processing", IF(CONTAINS("Completed", CHILDREN()), "Processing", IF(CONTAINS("N/A", CHILDREN()), "Processing", IF(CONTAINS("Canceled", CHILDREN()), "Processing", IF(COUNTIF(CHILDREN(), OR(@cell = "Completed", @cell = "N/A", @cell = "Canceled")) = COUNT(CHILDREN([email protected])),“完成”,“没有开始”))))))

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭

    Give this one a try:

    =IF(CONTAINS("Processing", CHILDREN()), "Processing", IF(CONTAINS("Pending", CHILDREN()), "Processing", IF(AND(COUNTIF(CHILDREN(), "Completed") + COUNTIF(CHILDREN(), "N/A") + COUNTIF(CHILDREN(), "Canceled") > 0, COUNTIF(CHILDREN(), "Completed") + COUNTIF(CHILDREN(), "N/A") + COUNTIF(CHILDREN(), "Canceled") < COUNT(CHILDREN([Primary Column]@row))), "Processing", IF(COUNTIF(CHILDREN(), "Completed") + COUNTIF(CHILDREN(), "N/A") + COUNTIF(CHILDREN(), "Canceled") = COUNT(CHILDREN([Primary Column]@row)), "Completed", "Not Started"))))

    Your column was calling anything containing completed as processing, regardless of other selections. You also mentioned "progressing" in your first post, but use "processing" in the formula in your last past. I used "processing" in this formula.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the形式ula Handbook template!
=[End Date]@row - [Start Date]@row<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108271,"type":"question","name":"Conditional formula - if a date is less than 30 days from today","excerpt":"I'm trying to create a conditional formula to flag if less than 30 days from today. I create a separate column to calculate but I am getting an error on the formula. =IF(AND([Event Date] > TODAY(), [Event Date] <= TODAY() + 30), \"Less than 30 days from today\", \"More than 30 days from today\") Help?","snippet":"I'm trying to create a conditional formula to flag if less than 30 days from today. I create a separate column to calculate but I am getting an error on the formula.…","categoryID":322,"dateInserted":"2023-07-28T18:21:58+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T19:22:21+00:00","insertUserID":159884,"insertUser":{"userID":159884,"name":"Connie Cochran","url":"https:\/\/community.smartsheet.com\/profile\/Connie%20Cochran","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T19:21:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":159884,"lastUser":{"userID":159884,"name":"Connie Cochran","url":"https:\/\/community.smartsheet.com\/profile\/Connie%20Cochran","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T19:21:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":25,"score":null,"hot":3381143059,"url":"https:\/\/community.smartsheet.com\/discussion\/108271\/conditional-formula-if-a-date-is-less-than-30-days-from-today","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108271\/conditional-formula-if-a-date-is-less-than-30-days-from-today","format":"Rich","lastPost":{"discussionID":108271,"commentID":387894,"name":"Re: Conditional formula - if a date is less than 30 days from today","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387894#Comment_387894","dateInserted":"2023-07-28T19:22:21+00:00","insertUserID":159884,"insertUser":{"userID":159884,"name":"Connie Cochran","url":"https:\/\/community.smartsheet.com\/profile\/Connie%20Cochran","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T19:21:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T19:21:56+00:00","dateAnswered":"2023-07-28T18:55:42+00:00","acceptedAnswers":[{"commentID":387890,"body":"

Try this:<\/p>

=IF(ISDATE([Event Date]@row), IF(AND([Event Date]@row > TODAY(), [Event Date]@row <= TODAY(30)), \"Less than 30 days from today\", \"More than 30 days from today\"), \"//www.santa-greenland.com/community/discussion/107787/\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108267,"type":"question","name":"Combining IF Formula for Blank\/ Not Blank Cells","excerpt":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected Date\" null -Pending: Based on \"Anticipated Collcted Date\" not null and \"Collected Date\" null Below is what I have, but it's unparseable:…","snippet":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected…","categoryID":322,"dateInserted":"2023-07-28T17:23:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164288,"lastUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":39,"score":null,"hot":3381135147,"url":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","format":"Rich","lastPost":{"discussionID":108267,"commentID":387885,"name":"Re: Combining IF Formula for Blank\/ Not Blank Cells","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387885#Comment_387885","dateInserted":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T18:30:11+00:00","dateAnswered":"2023-07-28T18:22:11+00:00","acceptedAnswers":[{"commentID":387882,"body":"

Greetings @brownrobe<\/a>,<\/p>

Here is a possible solution for your formula:<\/p>

=IF(NOT(ISBLANK([Recovery Collected Date]@row)), \"Complete\", IF(AND(ISBLANK([Recovery Collected Date]@row), ISBLANK([Anticipated Collection Date]@row)), \"Incomplete\", IF(AND(NOT(ISBLANK([Anticipated Collection Date]@row)), ISBLANK([Recovery Collected Date]@row)), \"Pending\", \"//www.santa-greenland.com/community/discussion/107787/\")))<\/p>

Please confirm I have your column names correct and adjust as needed.<\/p>

I hope this helps, and have a great weekend.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions