IF formula on parent line for if a child has a value

Emily McNeeley

I have a child parent relationship set up with an Ancestor column. I want the top Ancestor Line to fill in "Yes" in the "NDA" column if any child line has the contract type NDA

Emily McNeeley

@Heather Duff

That fixed it. Now I realize that I may have multiple parent lines, any way to update this so that if the child line says Yes in NDA that the parent does too?

So here the grandparent Microsoft line is blank

Heather Duff

@Emily McNeeley Easy peasy! So that you don't have to do a bunch of different formulas, you could do something like this:

=IF(COUNTIF(CHILDREN(),"Yes")>0,"Yes",IF(COUNTIF(CHILDREN([Contract Type]@row), HAS(@cell, "NDA")) > 0, "Yes", ""))

You should then be able to use this formula in both parent and grandparent rows in the NDA column. It translates to:

If any of the children of this row say "yes" in the NDA column, show Yes. Otherwise, if any of the children of this row have NDA in their Contract Type, show Yes. Otherwise, show blank.

Let me know if that works.

Heather Duff

Hi @Emily McNeeley ,

Try this:

=IF(COUNTIF(CHILDREN(NDA@row),"NDA")>0,"Yes","")

Let me know if it works for you!

Best,

Heather

Genevieve P.

Hi @Emily McNeeley

I can see that you marked Heather's answer as not working for you. Did you receive an error or an incorrect result? It would be helpful to see a screen capture of your sheet with the column names, but please block out sensitive data.

Cheers,

Genevieve

Emily McNeeley

Hi @Genevieve P.

No error, but it's not populating correctly. I have a child line with the Contract Type NDA but it does not populate "Yes" in the NDA column for the parent. I'm building a template so this is all dummy data.

Heather Duff

Hi @Emily McNeeley

Ah! Your Contract Type column is a multi-select column, which is why (in addition to the incorrect olumn name being in the children function) the formula is not working.

Try this:

=IF(COUNTIF(CHILDREN([Contract Type]@row), HAS(@cell, "NDA")) > 0, "Yes", "")

Let me know if it works!

Emily McNeeley

@Heather Duff

That fixed it. Now I realize that I may have multiple parent lines, any way to update this so that if the child line says Yes in NDA that the parent does too?

So here the grandparent Microsoft line is blank

Heather Duff

@Emily McNeeley Easy peasy! So that you don't have to do a bunch of different formulas, you could do something like this:

=IF(COUNTIF(CHILDREN(),"Yes")>0,"Yes",IF(COUNTIF(CHILDREN([Contract Type]@row), HAS(@cell, "NDA")) > 0, "Yes", ""))

You should then be able to use this formula in both parent and grandparent rows in the NDA column. It translates to:

If any of the children of this row say "yes" in the NDA column, show Yes. Otherwise, if any of the children of this row have NDA in their Contract Type, show Yes. Otherwise, show blank.

Let me know if that works.

Emily McNeeley

@Heather Duff That is perfect, thank you!

Heather Duff

@Emily McNeeley Happy to help!