IF statement for check mark box and parent cell

edited 04/14/22

Can anyone help me with creating a formula for the following thought:

Check mark yes in this row if the parent cell is a specific name?

Thanks!

Edit: Also want to add that there are multiple parent cells under this parent cell so I want to include all of them in the check box formula.

• ✭✭✭✭✭✭

If I'm reading this correctly, you want to check the box if the Task column in ANY parent/grandparent/great grandparent row above the given row in its hierarchy = "PSA", yes? Essentially this:

If so, here's the formula for your checkbox column:

=IF(COUNTIFS(ANCESTORS(Task@row), "PSA") > 0, 1, 0)

In English: Count if there are ancestor cells of the task column from this row that are equal to PSA, and if that count is greater than 0, check the box.

In the first row hierarchy grouping my screenshot above, because the top-most cell = "PSA", all the descendant rows in the hierarchy get checked. In the second row hierarchy grouping, the four checked rows all have a parent or grandparent row that is = "PSA", but other parent and child rows do not so they are not checked.

Regards,

Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭

Hey @gcolvis1

=IF(COUNT(CHILDREN())>0, IF([Column with Name in it]@row= "specific text you want", 1))

This says if the row has children (which means it's a parent) then, If the row has your specific text, mark it as 1 (which is a check). If not, do nothing (which leaves it unchecked). If the row wasn't a parent, also do nothing (which leaves it unchecked).

Be sure to edit the formula and insert your actual column name as well as the specific text (the text must be between the quote marks).

Does this work for you?

Kelly

• Hi Kelly,

That did not work and I don't think I was very clear with the question.

The column name that has the check boxes is "PSA Date".

The Parent cell is named PSA. The PSA Parent cell has multiple child cells and additional parent cells within the PSA drop down.

Does this make more sense? I was originally using =IF(PARENT(Task@row)="PSA",1) but it wasn't catching the everything within it.

Thanks!

• ✭✭✭✭✭✭

Hey

Go ahead and mark this one unresolved so it will get the attention again of the broader community.

• ✭✭✭✭✭✭

If I'm reading this correctly, you want to check the box if the Task column in ANY parent/grandparent/great grandparent row above the given row in its hierarchy = "PSA", yes? Essentially this:

If so, here's the formula for your checkbox column:

=IF(COUNTIFS(ANCESTORS(Task@row), "PSA") > 0, 1, 0)

In English: Count if there are ancestor cells of the task column from this row that are equal to PSA, and if that count is greater than 0, check the box.

In the first row hierarchy grouping my screenshot above, because the top-most cell = "PSA", all the descendant rows in the hierarchy get checked. In the second row hierarchy grouping, the four checked rows all have a parent or grandparent row that is = "PSA", but other parent and child rows do not so they are not checked.

Regards,

Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• Jeff,

That worked perfectly! Thanks!

• ✭✭✭✭✭✭

Regards,

Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• What if I wanted to do that formula with the checkbox on with PSA or EXP or TAP? Could I add commas after the "PSA" in your formula?

Thanks!

• ✭✭✭✭✭✭
edited 04/18/22

We'd have to change it up a bit, to use OR and @cell as part of the criteria within the COUNTIFS:

=IF(COUNTIFS(ANCESTORS(Task@row), OR(@cell = "PSA", @cell = "EXP", @cell = "TAP")) > 0, 1, 0)

Note: make sure the parentheses color-coding lines up.

Regards,

Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!