Mark child if Parent meets criteria

Hi-

I see lots of threads to mark a parent if a child meets a certain criteria, but I am looking for the opposite. I have Material, Labor, and Subcontractor Parent rows and would like to mark the children with either a number or letter indicating if it is a child of one of those parents (Material =1, Labor =2, Subcontractor =3)

Can someone please help with this functionality?

Thanks,

Mike

Find more posts tagged with

Sales

Construction

Finance

Formulas

Project Management

Comments

Paul Newcome

The children of each parent row are already indented underneath of it. Why are you trying to add a secondary marking to each row?

If you could give us some more info on what exactly you are looking to accomplish along with possibly some screenshots that would be extremely helpful towards finding a solution that works for you.

Mike Liggett

This is due to having multiple parents of the same type. See attached. If we could have a formula that automatically tags the identifier by row based on its parent...that is what I am after.

Screen Shot 2018-05-31 at 11.07.01 AM.png

L_123

=IF(PARENT([Primary Column]@row) = "Material", "M", IF(PARENT([Primary Column]@row) = "Labor", "L", IF(PARENT([Primary Column]@row) = "Sub", "S")))

L_123

Another solution that you could do is remove the identifier column and add

=SUM(CHILDREN(Cost@row))

to the parents in the cost columns. Then use a sumif to get all of those.

=SUMIF([Primary Column]:[Primary Column], "Material", Cost:Cost)

It's almost always best to reduce the amount of helper columns you have.

Paul Newcome

I am going to agree with Luke's second recommendation. The identifier column still seems redundant as each Child row is identified by the parent row already. The only thing I would change about that is you may want to use [Total Cost]:[Total Cost] to capture your markup.

If you do in fact still want the identifiers, then I would go with Luke's first suggestion.

Mike Liggett

Thanks for the feedback Paul and Luke and it works as intended! FYI the reason we need the identifier is because we have one more column that I did not show that has a building location we need to further filter by.

Sign in to join the conversation!

Quick Links

Trending discussions

Dropdown list restrictions
Is there a way to limit a dropdown list so that the values in the dropdown list can only be used once in a column?
seeking VLOOKUP or equivalent, search for most recent value, multiple dropdowns
My product/use case: I seek to build a camera inventory. Each camera has a unique identifier. Cameras are mobilized to different locations on different days. I need help making a VLOOKUP or equivalent combination of formulae functions to run 2 queries cross-referencing 2 grids, searching a column with multiple dropdown…
Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet