Manually Change Flags Within a Column Formula

Usman101
Usman101 ✭
in Formulas and Functions

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))

· Share on FacebookShare on Twitter

Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/18/24 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))))

    image.png

    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))))

    image.png

    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.

    · Share on FacebookShare on Twitter

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/18/24 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))))

    image.png

    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))))

    image.png

    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.

    · Share on FacebookShare on Twitter
  • Usman101
    Usman101 ✭

    @Mark.poole Thanks for the help, I will try and use this

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!