# Parent/Children Flag Formula

Options

Hi all,

I'm trying to create a formula that will flag a child row if a certain date is within a # of number of days, but I'd also like it to flag the parent row if any of the children row are flagged. I am currently using these two formulas: for the parent row - =IF(COUNTIF(CHILDREN(), =1) > 0, 1, 0) . . . and for the child row - =IF(AND([Credentialed End Date]@row <= TODAY(45), [Credentialed End Date]@row >= TODAY()), 1, 0)

I would really like to use one formula that can be made into a column formula so I don't have to remember to copy the formula to any added rows. Any thoughts?

• ✭✭✭✭✭✭
Options

Had to put the logic in the right order and convert the AND to an OR to get the right response. Try this:

=IF(Level@row = 1, IF(OR([Credentialed End Date]@row <= TODAY(), TODAY(45) >= [Credentialed End Date]@row), 1, IF(COUNTIF(CHILDREN(), =1) > 0, 1, 0)))

It also works as a column formula.

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭✭
Options

Try this formula in your flag column:

=IF(COUNT(Ancestors())=0, IF(COUNTIF(CHILDREN(), =1) > 0, 1, IF(AND([Credentialed End Date]@row <= TODAY(45), [Credentialed End Date]@row >= TODAY()), 1, 0)))

The formula uses the ancestors count to identify parent rows and then uses your parent flag formula. If the row isn't a parent it uses your child formula.

Work?

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• Options

Hi @Mark Cronk ,

Thanks for replying. Unfortunately, I am receiving a "circular reference" error in the parent cell and "blocked" in the children when the formula is converted to a column formula (or if I extend it to more than one cell.

• ✭✭✭✭✭✭
edited 12/30/20
Options

Sorry, I was trying to avoid a helper column and it failed. Try inserting a text/number helper column [Level] and insert the formula =COUNT(ANCESTORS()). You should get a 0 in Parent rows and a 1 in children; again, assumes 2 tier hierarchy.

Then in your checkbox column enter this formula:

=IF([Level]@row=0, IF(COUNTIF(CHILDREN(), =1) > 0, 1, IF(AND([Credentialed End Date]@row <= TODAY(45), [Credentialed End Date]@row >= TODAY()), 1, 0)))

Work this time?

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭✭
Options

I would suggest a helper column that identifies child vs parent rows then using a variation of @Mark Cronk's solution where the initial argument in the IF statement evaluates what is in the helper column to determine which of the two formulas to run.

• edited 12/30/20
Options

Well, the errors are gone with the addition of the LEVEL column, but it isn't flagging correctly. There are rows with dates that are within 45 days that were flagged with my previous formulas and with the new formulas are not. See screenshot.

• ✭✭✭✭✭✭
Options

Had to put the logic in the right order and convert the AND to an OR to get the right response. Try this:

=IF(Level@row = 1, IF(OR([Credentialed End Date]@row <= TODAY(), TODAY(45) >= [Credentialed End Date]@row), 1, IF(COUNTIF(CHILDREN(), =1) > 0, 1, 0)))

It also works as a column formula.

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!