Parent Row Status Formula

dhall

Hi all,

There are tons of parent status row formulas on this site and I have to first say a huge thank you to the contributors because you've helped me resolve my own problems without opening a new question. Here is my current formula based on the great input I've read from the community:

=IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Complete"), "Complete", IF(OR(CONTAINS("In Progress", CHILDREN()), AND(COUNTIF(CHILDREN(), "Complete") > 0, COUNTIF(CHILDREN(), "Not Started") >= 0, COUNTIF(CHILDREN(), "Upcoming") >= 0)), "In Progress", IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Upcoming"), "Upcoming", "Not Started")))

I'm posting here in case it helps someone in the future using similar statuses as we are.

However, the reason I opened this question is something related to this. Is there a way to lock just the Status Cell of a row from being manually edited if it is considered a Parent row?

I'd like to prevent users from accidentally modifying the status of the parent row and wiping out the formula. I can't lock the row and I can't lock the column as it is needed on other columns or rows, respectively.

Another use case, I've had automations wipe out my formulas in the cell and so I've had to go back in and fix it.

I know I'm asking for a feature that probably doesn't exist, but I'm asking just to get the conversation started. I think it would be super beneficial to have certain cells locked to a formula if the row is detected to be a parent.

Paul Newcome

Is there some type of logic you can use to automate the status on the child rows as well? You could then create a column formula which cannot be edited/deleted.

dhall

Hi Paul, thanks for the response and apologies for the delay in replying.

Right now, we only have two automations that impacts the Status column.

1) If someone marks the checkbox that the task is complete, then automation updates the Status for that row as complete.

2) If the status is "Not Started", "Start Date" is within 7 days, and the "Assigned To" field is not blank, automation changes the status to "Upcoming" so that assigned users can plan accordingly.

Other than those two, users are manually changing the child objects to their respective status. I've preached "don't touch the parent rows, let the sheet do all the work!" but there will always be user error.

Any suggestions on how to make it so they don't need to touch the status column at all?

Paul Newcome

What are the other Statuses and the logic for each?

dhall

Not Started - Default - Task hasn't begun yet.

Upcoming - Automatically changed via automation. Task is Not Started and Start Date is within the next 7 days.

In Progress - Manually changed to In Progress once work on it has begun.

Canceled - Manually changed to Canceled if something causes that task to no longer be relevant/needed for the project.

On Hold - Manually changed if the task or entire project is considered "On Hold" due to some external factors.

Complete - Automatically changed via automation (checkbox).

Paul Newcome

I would suggest having separate columns for each status. I usually use dates and have users enter the date at which they started or finished the task. On hold and Canceled I usually use a flag for.

Then you can write a nested IF statement that drives all of the different statuses and have visibility into when it went from one to another.