IF statement for check mark box and parent cell

GabyC

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.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/326186#Comment_326186

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.

Kelly Moore

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

GabyC

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!

Kelly Moore

Hey

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

Jeff Reisman

https://community.smartsheet.com/discussion/comment/326186#Comment_326186

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.

GabyC

Jeff,

That worked perfectly! Thanks!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/326444#Comment_326444

Glad I could help!

GabyC

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!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/326450#Comment_326450

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.