automatically update cell based on other record
![mrmik](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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
-
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
-
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,
Thank you so much. This works.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!