can an automation change a cell value in the parent row?

Soueif

hi everybody…

I'm using an automation to "Change cell value" when a row is changed. The automation can only change a cell on the same row that was changed.

Is there any way to set an automation to change a cell value in another row? Specifically, in the parent row of the changed row?

Thanks in advance

jessica.smith

It's difficult to give a specific solution without seeing your sheet, but I think you could use some helper columns and formulas to trigger the automation from the parent. You would basically need the change you are trying to detect on the child row also cause a change in the parent row and to be able to distinguish between the child and parent row so that the automation triggers on the parent only.

Soueif

Thank you Jessica,

well, I don't want to put you through too much trouble, but OK, the sheet is quite simple really, here's an image

so, I've created an automation that: when "Start" is checked, it selects a certain value in the "Stage" column in the same row where "Start" was checked. But what I really need is to set a value in the "Stage" column in the parent row (titled "Research-Report workflow template").

So if you can give me some more specific guidance, that would be great…

Thanks in advance

Paul Newcome

Does the Stage column ever need to be manually updated, or is the intent that it is fully automated?

jessica.smith

Would somthing like this work?

Add a column formula to the Stage column that looks at the status of children to determine the stage of the parent?

Column formula in Parent colum (could be hidden column):

=IF(COUNT(ANCESTORS([Task Name]@row)) = 0, [Task Name]@row, PARENT([Task Name]@row))

Column formula in Stage column:

=IF(COUNT(ANCESTORS()) = 0, IF(COUNTIFS(Start:Start, 1, Parent:Parent, Parent@row) = 0, "Planned", IF(COUNTIFS(Start:Start, 1, Parent:Parent, Parent@row) > 0, "In Progress")), "")

Soueif

@Paul Newcome ideally the Stage column would be fully automated, but if I can't reach a perfect automation then I might need to sometimes update it manually on the parent row.