Manually Change Flags Within a Column Formula
So I have a formula that checks if the row is a certain type "Scope" and then checks if any of the children "At Risk" Flags are checked. If so then it will check the Parent "Scope" Flag as well. But I then want to apply this formula to all parents in the sheet instead of putting it in manually. If I change to a column formula it doesn't let me check the child "At Risk" flags manually? What to do here thanks.
Formula: =IF(Structure@row = "Scope", IF(COUNTIF(CHILDREN(Flag@row), 1) > 0, 1, 0))
Best Answer
-
@Usman101 the only ways to accomplish this is to minimize all the children rows. Then you can drag the formula to all the parent rows, or you can create a helper row that you actually check. Then adjust the formula to look at that row if it is a child. Then count for hierarchy. If you only have 1 level of hierarchy. the below formula will work
=IF(Helper@row = 1, 1, IF(COUNT(ANCESTORS(Helper@row)) = 0, IF(Structure@row = "Scope", IF(COUNTIF(CHILDREN(Flag@row), 1) > 0, 1, 0))))
how ever if you have multiple levels of hierarchy you need a helper row to count it.
=count(Ancestors())
Then use the below formula
=IF(Helper@row = 1, 1, IF(Ancestors@row < MAX(Ancestors:Ancestors), IF(Structure@row = "Scope", IF(COUNTIF(CHILDREN(Flag@row), 1) > 0, 1, 0))))
If you found this comment helpful. Please respond with any of the buttons below. Awesome๐ค, Insightful๐ก, Upvoteโฌ๏ธ, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
Answers
-
@Usman101 the only ways to accomplish this is to minimize all the children rows. Then you can drag the formula to all the parent rows, or you can create a helper row that you actually check. Then adjust the formula to look at that row if it is a child. Then count for hierarchy. If you only have 1 level of hierarchy. the below formula will work
=IF(Helper@row = 1, 1, IF(COUNT(ANCESTORS(Helper@row)) = 0, IF(Structure@row = "Scope", IF(COUNTIF(CHILDREN(Flag@row), 1) > 0, 1, 0))))
how ever if you have multiple levels of hierarchy you need a helper row to count it.
=count(Ancestors())
Then use the below formula
=IF(Helper@row = 1, 1, IF(Ancestors@row < MAX(Ancestors:Ancestors), IF(Structure@row = "Scope", IF(COUNTIF(CHILDREN(Flag@row), 1) > 0, 1, 0))))
If you found this comment helpful. Please respond with any of the buttons below. Awesome๐ค, Insightful๐ก, Upvoteโฌ๏ธ, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
@Mark.poole Thanks for the help, I will try and use this
Help Article Resources
Categories
Check out the Formula Handbook template!
