Protect formulas within cells in a single column without using column formula or locking the column

JMPB

Objective: Status column is a drop-down. The cells in the Status column have a formula using Descendants, with the idea that when the descendants tasks are all marked with the same status, such as "Completed," then the ancestor to those descendants is also auto-marked as "Completed."

Example: See screenshot below. When descendants Task 1 and Task 2 are marked as "Completed," their ancestor task Parent A automatically updates to "Completed." And once Task 3 and Task 4 are both marked "Completed," then both Parent B and Grandfather would be auto-updated to "Completed".

Issue: the formulas get inadvertently deleted when someone chooses a value from the dropdown in the Status column.

What didn't work: I can't use a column formula because then the drop-down entries are not available. Also, if I lock the column, then non-admins can't use the dropdown.

Question: How can I protect the formulas while still allowing everyone to select dropdown entries?

Jgorsich

Fundamentally a drop down will overwrite the formulas if it is in the same column, having this all in the same column will never achieve what you are looking for. That being said, your end goal may be doable… Presuming that your goal is to have a column that reflects status as a function of the formulas OR as a function of a drop down (with the drop down overwriting the formula value if entered) you can achieve this by adding another column called "ManualStatus".

Put your dropdown in the ManualStatus column and modify your formula in your status column by having it FIRST check to see if the value on that row in ManualStatus is blank ("if(ManualStatus@row="",….") . If it IS blank, find the value per your existing formula. If it ISN'T blank, use the value from ManualStatus@row.

This way you can use the dropdown without deleting formulas (and you can edit and delete the entry from the drop down and have that reflected accordingly).