Needing some help with my current smartsheet project
So I'm coming across some issues with my workflows and functions with my current sheet, and I'm hoping somebody could help me out because I'm stumped.
There are boxes I have set up on children rows that get checked manually to confirm a certain portion of the Main Task is complete.
I'm currently in search of a way I can get a box checked on the parent row when the corresponding box is checked on the child row.
Because there are so many tiny tasks that go along to fit the main goal of the projects we do I would like to be able to have this information all accessible on the parent row. That way I can keep track of the progress on these jobs.
我有单词查找树d using the function on the entire column, but it automatically becomes invalid once I manually check the box.
I'm wondering if there is a way I can reference the children rows, but from a separate column, and if there is, what the base of that function would look like?
I'm open to any suggestions
Best Answer
-
Darren Mullen ✭✭✭✭✭✭
@SarahIYes you can do that just use CHILDREN([COLUMN NAME])
Darren Mullen "Smartsheet Guru", Member,Smart Consulting Services LLC
Get my7 Smartsheet tips here
Answers
-
Paul Newcome ✭✭✭✭✭✭
You would use
CHILDREN([Column Name]@row)
to reference the child rows in another column.
thinkspi.com
-
Darren Mullen ✭✭✭✭✭✭
@SarahIYes you can do that just use CHILDREN([COLUMN NAME])
Darren Mullen "Smartsheet Guru", Member,Smart Consulting Services LLC
Get my7 Smartsheet tips here
-
SarahI ✭
=IF(COUNTIF(CHILDREN([Meter (Helper)]@row), 1) > 0, 1, 0)
Heres the formula I got to work Thanks!
-
Paul Newcome ✭✭✭✭✭✭
@Darren MullenDon't forget the row indicator within the CHILDREN function.
thinkspi.com
-
SarahI ✭
@Paul NewcomeI think I might change it a little bit and have it shown as dates instead.
The child rows get a date in the [helper column] when [Service Type] and [Status] are specific inputs.
From there I just want that same date to show up in a separate column along the parent row
I really appreciate the help
-
Paul Newcome ✭✭✭✭✭✭
@SarahIThe exact formula would depend on the structure and workflow. Could there be multiple different dates within the child rows for a single parent? If so, how would you determine which one to pull? Are you able to provide screenshots for context?
thinkspi.com
-
SarahI ✭
Sorry if this is kind of confusing, I'm in the progress of organizing this.
Sometimes the value isn't a date, (I put NA) when I'm unsure or it doesn't matter.
It is possible that there may be more than one value in the child rows, but technically with the way I have been running it there shouldn't be and it would be an accident on my end.
I basically just want it to completely copy whatever value is in the child row that's not blank at a corresponding column, if there is more than one child row that is not blank I would want it to say something like "Error"
Hope this makes sense
-
Paul Newcome ✭✭✭✭✭✭
Try something like this:
=IF(COUNTIFS(CHILDREN([Job Address]@row)> 0, IF(COUNT(DISTINCT(CHILDREN([Date Completed]@row))) = 1, INDEX(COLLECT(CHILDREN([Date Completed]@row), CHILDREN([Date Completed]@row), @cell <> ""), 1), "ERROR - More than 1 different value in child rows"))
thinkspi.com
Help Article Resources
Categories
Double check your cross sheet references. Make sure they are all single columns by clicking on the appropriate column header.<\/p>
<\/p>
When doing this, give the sheet a little time to load before selecting the column. Sometimes moving too fast allows you to select a column header before the sheet fully loads in the creator window. Then when the sheet does finally completely load in, the selection automatically reverts to the home cell (top right corner). This happens to me quite a bit when I am in a hurry.<\/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":106940,"type":"question","name":"IF cell CONTAINS one\/two\/three different values, return corresponding values","excerpt":"Hello community, I am attempting to return single or multiple values depending on a multiple dropdown column. My dropdown column contains criteria \"Consolidation\", \"Reduction\", \"Termination\", \"New\" Currently the formula works for single values with the following formula =IF([Type of Project]@row = \"Consolidation\", \"🝢\",…","snippet":"Hello community, I am attempting to return single or multiple values depending on a multiple dropdown column. My dropdown column contains criteria \"Consolidation\", \"Reduction\",…","categoryID":322,"dateInserted":"2023-06-27T09:24:42+00:00","dateUpdated":null,"dateLastComment":"2023-06-27T12:11:45+00:00","insertUserID":143463,"insertUser":{"userID":143463,"name":"Sam Swain","url":"https:\/\/community.smartsheet.com\/profile\/Sam%20Swain","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T20:08:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":151203,"lastUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T12:11:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":53,"score":null,"hot":3375727587,"url":"https:\/\/community.smartsheet.com\/discussion\/106940\/if-cell-contains-one-two-three-different-values-return-corresponding-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106940\/if-cell-contains-one-two-three-different-values-return-corresponding-values","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106940,"commentID":382505,"name":"Re: IF cell CONTAINS one\/two\/three different values, return corresponding values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382505#Comment_382505","dateInserted":"2023-06-27T12:11:45+00:00","insertUserID":151203,"insertUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T12:11:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/Q6RJIERQGF1Y\/screenshot-2023-06-27-at-10-22-29.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot 2023-06-27 at 10.22.29.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-27T10:17:00+00:00","dateAnswered":"2023-06-27T10:12:21+00:00","acceptedAnswers":[{"commentID":382490,"body":"