Is there a way to trigger a flag in the header row when there is 1 or more flags in the column?

Alicia D

I am trying to see if there is a way to have the flag in the title row go off when any flags are triggered in the sub rows below. In the photo attached, I would be referring to the blue row that would flag since the other white rows below it have 1 or more flags. I am not sure if this is possible, but figured this was the place to ask!

Samuel Mueller

So create a second column called Helper At Risk and put this formula in that column:

=COUNT(ANCESTORS())

Then replace the formula in the At Risk Column with this:

=IF([Helper At Risk]@row <> 0, IF(AND([End Date]@row <= TODAY(0), Complete@row <> "Complete", Status@row <> ""), 1, 0), IF(COUNTIFS(CHILDREN(), 1) > 0, 1, 0))

and let me know if that meets your needs..

Samuel Mueller

@Alicia D I wonder if you hierarchy is different than the last situation - if you make this change does that work?

=IF([Helper At Risk]@row >1, IF(AND([End Date]@row <= TODAY(0), Complete@row <> "Complete", Status@row <> ""), 1, 0), IF(COUNTIFS(CHILDREN(), 1) > 0, 1, 0))

we may need to update alter formula still.

Samuel Mueller

In a helper column you could have a formula, something like

=if(countifs(children([At Risk}@row),1)>0,"Flag Checked","")

then you could create an automation where if a value in the helper row = "Flag Checked" then check the box. However you can't use an automation for a flag, you would have to change it to a checkbox. Or you could just put this formula in the parent rows:

=if(countifs(children(),1)>0,1,0)

Samuel Mueller

It also looks like that column is already has a column formula, so there may be more details needed to solve this.

Alicia D

Thank you! How do you create a helper column? @Samuel Mueller

Samuel Mueller

@Alicia D a helper column would just be an additional column with a formula in it to reference for another column. What formula do you have in the At Risk column currently?

Alicia D

@Samuel Mueller Here is my current formula in that At Risk column: =IF(AND([End Date]@row <= TODAY(0), Complete@row <> "Complete", Status@row <> ""), 1, 0)

Samuel Mueller

So create a second column called Helper At Risk and put this formula in that column:

=COUNT(ANCESTORS())

Then replace the formula in the At Risk Column with this:

=IF([Helper At Risk]@row <> 0, IF(AND([End Date]@row <= TODAY(0), Complete@row <> "Complete", Status@row <> ""), 1, 0), IF(COUNTIFS(CHILDREN(), 1) > 0, 1, 0))

and let me know if that meets your needs..

Alicia D

This worked, thank you @Samuel Mueller !

Alicia D

Hello @Samuel Mueller , this formula was working, but is not on a new sheet that I have created. I used the helper at risk column with the formula =COUNT(ANCESTORS()), as well as set the flag column with the formula: =IF([Helper At Risk]@row <> 0, IF(AND([End Date]@row <= TODAY(0), Complete@row <> "Complete", Status@row <> ""), 1, 0), IF(COUNTIFS(CHILDREN(), 1) > 0, 1, 0)) like you mentioned.

Here is a screenshot of what I am seeing on my end

Samuel Mueller

@Alicia D I wonder if you hierarchy is different than the last situation - if you make this change does that work?

=IF([Helper At Risk]@row >1, IF(AND([End Date]@row <= TODAY(0), Complete@row <> "Complete", Status@row <> ""), 1, 0), IF(COUNTIFS(CHILDREN(), 1) > 0, 1, 0))

we may need to update alter formula still.

Samuel Mueller

@Alicia D Maybe try this

=IF(COUNT(CHILDREN()) = 0, IF(AND([End Date]@row <= TODAY(0), Complete@row <> "Complete", Status@row <> ""), 1, 0), IF(COUNTIFS(CHILDREN(), 1) > 0, 1, 0))

Alicia D

This worked, thank you @Samuel Mueller !

Samuel Mueller

@Alicia D if that second formula worked, you can delete the at risk helper column - unless you are using it for something else.