从女儿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.
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?
Best Answer
-
Carson Penticuff ✭✭✭✭✭
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 ✭✭✭✭✭
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!
-
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 ✭✭✭✭✭
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?
-
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 ✭✭✭✭✭
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
Categories
Check out the形式ula Handbook template!
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":"