automatically update cell based on other record

mrmik

Within the same sheet, there are two row types : Header and Detail. There is a Report Name Column that group these record together. Based on the image below. If record1 (Report01 and RowType = Header) has a Status = Approve, I would like all detail records under the same Report Name (Report01) to have Status = Approve automatically.

Is this possible?

Thank you

Kelly Moore

Hey @mrmik

You should be able to accomplish this with a helper column (a checkbox will do) and Change Cell Value automation. You can name the Helper column anything you like, and once the formula is built you can shove the column to the right and hide it, if desired. (A helper column is an added column that is used for to assist formulas or workflows but isn't necessarily needed for anything else)

Helper Column

=IF([Row Type]@row = "Detail", IF(COUNTIFS([Row Type]:[Row Type], "Header", [Report Name]:[Report Name], [Report Name]@row, Status:Status, "Approve") > 0, 1))

This will check the Detail rows once the relevant Header row has a response of "Approve".

You will also need to use the Change Cell Value Automation. I'll call the helper column "Checkbox Col" - be sure to update your automation to match your actual helper column name.

Once the formula above checks the boxes, this automation will add the word "Approve" to the specific rows.

Will this work for you?

Kelly

Kelly Moore

Hey @mrmik

You should be able to accomplish this with a helper column (a checkbox will do) and Change Cell Value automation. You can name the Helper column anything you like, and once the formula is built you can shove the column to the right and hide it, if desired. (A helper column is an added column that is used for to assist formulas or workflows but isn't necessarily needed for anything else)

Helper Column

=IF([Row Type]@row = "Detail", IF(COUNTIFS([Row Type]:[Row Type], "Header", [Report Name]:[Report Name], [Report Name]@row, Status:Status, "Approve") > 0, 1))

This will check the Detail rows once the relevant Header row has a response of "Approve".

You will also need to use the Change Cell Value Automation. I'll call the helper column "Checkbox Col" - be sure to update your automation to match your actual helper column name.

Once the formula above checks the boxes, this automation will add the word "Approve" to the specific rows.

Will this work for you?

Kelly

mrmik

Kelly,

Thank you so much. This works.