Referencing a flag column in a Function

nlarson_PMC

Hi everyone,

I'm currently in the process of adding functions to my task list. I have two columns that I am trying to edit the functions of. One, (Needs Following Up) has a "priority" symbol to mark if the parent row has a child that is a follow up item. The other (Follow Up Item) marks if a row contains "follow up" in either it's 'Item Name' or 'Details & Additional Notes' cells.

As I have it right now, the formula for the 'Needs Following Up' column is similar to the 'Follow Up Item' the only difference is that the first formula references its children's cells for the words "follow up."

For Reference these are the following formulas:

'NEEDS FOLLOWING UP' Column: =IF(AND(OR(CONTAINS("Follow up", CHILDREN([Item Name]@row)), CONTAINS("Follow up", CHILDREN([Details & Additional Notes]@row))), NOT(HAS([Item Type]@row, "HEADER"))), "High", "")

'FOLLOW UP ITEM' Column: =IF(OR(CONTAINS("follow up", [Item Name]@row), CONTAINS("follow up", [Details & Additional Notes]@row)), 1, "-")

What I hope to do is for the NEEDS FOLLING UP column to show as High priority if its children's row(s) are flagged.

Additionally I would like to add that if the STATUS cell (a single-select, dropdown column) is marked as "Complete" then the FOLLOW UP ITEM row is no longer flagged.

Thanks!

Genevieve P.

Hi @nlarson_PMC

First, lets adjust your Flag column to exclude any of the rows where the Status is Complete. We can change the formula in your "FOLLOW UP ITEM" column to be as follows:

=IF(AND(OR(CONTAINS("follow up", [Item Name]@row), CONTAINS("follow up", [Details & Additional Notes]@row)), Status@row <> "Complete"), 1, "-")

<> this means "not equal to", so we're adding in a condition that Status should not be equal to Complete in order for the flag to appear:

Then since you're already doing the hard work of filtering down what cells have "Follow Up" in your flag column, you can simplify your "NEEDS FOLLOWING UP" to only look at the Children that have flags!

=IF(AND(COUNTIF(CHILDREN([FOLLOW UP ITEM]@row), 1) > 0, NOT(HAS([ITEM TYPE]@row, "HEADER"))), "High", "")

Since we're filtering based on the flag column, and this flag column is already excluding the "Complete" Status, then as the Flag adjusts so will this other formula:

You can then turn these in to Column Formulas. Let me know if this is what you were looking to do!

Cheers,

Genevieve

Genevieve P.

Hi @nlarson_PMC

First, lets adjust your Flag column to exclude any of the rows where the Status is Complete. We can change the formula in your "FOLLOW UP ITEM" column to be as follows:

=IF(AND(OR(CONTAINS("follow up", [Item Name]@row), CONTAINS("follow up", [Details & Additional Notes]@row)), Status@row <> "Complete"), 1, "-")

<> this means "not equal to", so we're adding in a condition that Status should not be equal to Complete in order for the flag to appear:

Then since you're already doing the hard work of filtering down what cells have "Follow Up" in your flag column, you can simplify your "NEEDS FOLLOWING UP" to only look at the Children that have flags!

=IF(AND(COUNTIF(CHILDREN([FOLLOW UP ITEM]@row), 1) > 0, NOT(HAS([ITEM TYPE]@row, "HEADER"))), "High", "")

Since we're filtering based on the flag column, and this flag column is already excluding the "Complete" Status, then as the Flag adjusts so will this other formula:

You can then turn these in to Column Formulas. Let me know if this is what you were looking to do!

Cheers,

Genevieve

nlarson_PMC