Prevent Cell Formula from being overwritten

Lucas Argueza

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([email protected] = "MT", COUNTIF(CHILDREN(), OR(@cell = "Green", @cell = "Gray")) = 5), "Green")

Any help is appreciated. Thank you in advance.

Screenshot (3).png

Heather Duff

Hi Lucas,

I would have suggested the Column Formula, but it sounds like that isn't working for you. I'm curious to know why it wipes everything in that column when doing so...

My other suggestion is to lock the column. However, those who are owners or admins on the sheet will still be able to edit the cells.

Hope this helps!

Best,

Heather

Paul Newcome

You cannot have manual entry AND a formula in the same cell at the same time. One will always override and delete the other.

That is why when a user manually changes it, the formula is lost and also why applying a column formula removes the manually entered data.

Lucas Argueza

Thank you @Paul Newcome & @Heather D for the quick response.

I'm not sure why the column formula acted the way it did. I just apply the cell formula above and everything in that column is removed and cannot be edited manually. Good thing I tested this on a copy of the original sheet. I guess column formulas cant be overwritten either

Paul Newcome

Column formulas cannot be overwritten because the formula is applied to every cell within that column. If it is manually overwritten in even one row, then it is no longer a column formula.

Using the cell formula applies it on a row by row basis (as opposed to the entire column) but can still be overwritten/deleted by manual entry.