Prevent Cell Formula from being overwritten

I have a cell formula that will change the status of the parent cell (in this case Materials) to Green, when all of its children are either Green or Gray. However, since the sheet is shared amongst multiple people, it can be overwritten manually when someone changes the status to a different color then the properties of the formula are lost. I was wondering if there was a way to prevent the formula from being overwritten or some way for the status to be changed freely except for when the conditions are met and then the parent status changes. Hopefully, I got my point across. Screenshot of an example layout below.

I tried changing the cell formula to column formula to see if that would work, but it doesn't. It wipes everything in the column and cells cannot be edited.

This is the formula that I have have so far:

=IF(AND(TCode@row = "MT", COUNTIF(CHILDREN(), OR(@cell = "Green", @cell = "Gray")) = 5), "Green")

Any help is appreciated. Thank you in advance.


Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!