Automatically change Parent status to display Children status; still be able to change Child value

clairehunter

Hello,

I have this formula =IF(CONTAINS("In Progress", CHILDREN()), "In Progress", IF(AND(COUNTIF(CHILDREN(), "Completed") = COUNT(CHILDREN()), COUNTIF(CHILDREN(), "") = 0), "Completed", IF(OR(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), COUNTIF(CHILDREN(), "Not Started") + COUNTIF(CHILDREN(), "") = COUNT(CHILDREN())), "Not Started", "In Progress"))) it works great, however, I would like to still be able to change the status of the Child row. How would I go about adding that to this preexisting formula? Thanks so much!

Genevieve P.

Hi @clairehunter

As long as you just paste the formula into Parent rows, you'll be able to manually change the child rows below! 🙂

Only Column Formulas will block cells from being edited manually. Does that make sense?

Cheers,

Genevieve

clairehunter

@Genevieve P.

Yes, this makes sense. It would not be optimal conditions to have to manually enter the formula into each parent formula as we will have many being added each day. Is there a way to tell the formula to allow edits while doing the action I suggested?

Genevieve P.

Hi @clairehunter

There currently isn't a way to identify specific cells as editable while applying a default column formula to all other cells.

Either the entire column has the same formula in every cell (as a Column Formula), or you can edit cells (meaning some can have formulas input and some can have a manual input).

As an alternative, you could have the formula in a different column and reference the Status column within your CHILDREN functions instead. For example:

=IF(CONTAINS("In Progress", CHILDREN(Status@row)), "In Progress",...

This would keep your entire Status column as editable, while locking this second column in to a formula.

Cheers,

Genevieve

JamesB

@clairehunter

You could use this formula process with helper columns, and then an automation to update the status of the parent rows. Then have the automation run on a hourly cadence to update/replace the parent row value if they were manually changed or if the status actually changes.