automatically update cell based on other record

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


Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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
    mrmik ✭✭

    Kelly,

    Thank you so much. This works.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!