Needing some help with my current smartsheet project

SarahI

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.

I have tried 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

Darren Mullen

@SarahI Yes you can do that just use CHILDREN([COLUMN NAME])

Paul Newcome

You would use

CHILDREN([Column Name]@row)

to reference the child rows in another column.

Darren Mullen

@SarahI Yes you can do that just use CHILDREN([COLUMN NAME])

SarahI

=IF(COUNTIF(CHILDREN([Meter (Helper)]@row), 1) > 0, 1, 0)

Heres the formula I got to work Thanks!

Paul Newcome

@Darren Mullen Don't forget the row indicator within the CHILDREN function.

SarahI

@Paul Newcome I 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

@SarahI The 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?

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"))